﻿using BonjourQuotation.DataAccess.Entity_Framework;
using BonjourQuotation.ObjectModel.AccountData;
using BonjourQuotation.ObjectModel.BusinessData;
using BonjourQuotation.ObjectModel.ErrorData;
using BonjourQuotation.ObjectModel.LoggerData;
using BonjourQuotation.ObjectModel.ReportData;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BonjourQuotation.DataAccess.Repositories
{
    public class ReportRepository : IReportRepository
    {
        private readonly BonDataEntities _context = new BonDataEntities();
        private readonly log4net.ILog logger = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        public RoomingListReportBData RoomingListReport(int GroupID, int VendorID, DateTime ArrivalDate)
        {
            RoomingListReportBData data = new RoomingListReportBData();
            data.Rooms = new List<RoomingListReportRoomsBData>();

            try
            {
                if (VendorID == 0)
                {
                    data.Rooms = (from room in _context.tblGroupRooms
                                  from serv in _context.tblServices
                                  where room.GroupID == GroupID && serv.ServiceTypeID == 1 && room.ServiceID == serv.ServiceID
                                  orderby room.ServiceID, room.Names
                                  select new RoomingListReportRoomsBData()
                                  {
                                      Names = room.Names,
                                      ServiceID = room.ServiceID,
                                      ServiceName = serv.ServiceCode,
                                      Title = room.Title,
                                      NumOfPerson = room.NoOfPerson,
                                      NumOfPersonRoom = room.NoOfPersonRoom,
                                      Notes = room.Notes,
                                  }).ToList();
                }
                else
                {
                    data.Rooms = (from room in _context.tblVConfirmRooms
                                  from serv in _context.tblServices
                                  where room.GroupID == GroupID && serv.ServiceTypeID == 1 && room.ServiceID == serv.ServiceID && room.VendorID == VendorID
                                  orderby room.ServiceID, room.Names
                                  select new RoomingListReportRoomsBData()
                                  {
                                      Names = room.Names,
                                      ServiceID = room.ServiceID,
                                      ServiceName = serv.ServiceCode,
                                      Title = room.Title,
                                      NumOfPerson = room.NoOfPerson,
                                      Notes = room.Notes,
                                      //  NumOfPersonRoom = room.NoOfPersonRoom,
                                  }).ToList();
                }
                var Grp = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
                var Company = _context.tblCompanies.Where(comp => comp.CompanyCode == "BONJOUR").SingleOrDefault();
                if (Company != null)
                {
                    data.CompanyName = Company.CompanyName;
                    data.CompanyInfo = Company.Address + ", " + Company.City + ", " + Company.StateID + ", " + Company.PostalCode + ", Phone " + Company.PhoneNumber + ", Fax " + Company.FaxNumber;
                }
                if (Grp != null)
                {
                    data.GroupName = Grp.GroupName;
                    data.GroupCode = Grp.GroupCode;
                    data.Nationality = _context.tblCountries.Where(ctr => ctr.CountryID == Grp.CountryID).SingleOrDefault().Nationality;
                }
                if (VendorID != 0)
                {
                    var VendConf = _context.tblVendorConfirms.Where(vndC => vndC.GroupID == GroupID && vndC.VendorID == VendorID && vndC.ArrivalDate == ArrivalDate).SingleOrDefault();
                    var Vendor = _context.tblVendors.Where(vnd => vnd.VendorID == VendorID).SingleOrDefault();
                    if (Vendor != null)
                    {
                        data.VendorName = Vendor.VendorName;
                        data.VendorContactNameTitle = Vendor.ContactName + " /" + Vendor.ContactTitle;
                        data.VendorPhoneSales = Vendor.PhoneSales;
                        data.VendorFax = Vendor.FaxSales;
                        data.VendorEmailSales = Vendor.Note;
                    }
                    if (VendConf != null)
                    {
                        data.ArrivalDateTime = VendConf.ArrivalDate.ToString("dd-MMM-yy");// +" " + VendConf.ArrivalTime != null ? Convert.ToDateTime(VendConf.ArrivalTime).ToString("h:mm tt") : "";
                        data.DepartureDateTime = VendConf.DepartureDate != null ? Convert.ToDateTime(VendConf.DepartureDate).ToString("dd-MMM-yy") : "" + " " + VendConf.DepartureTime != null ? Convert.ToDateTime(VendConf.DepartureTime).ToString("h:mm tt") : "";
                        data.PorterageNotes = VendConf.PorterageNotes;
                        data.RoomListNotes = VendConf.RoomListNotes;
                        data.RoomListEditNotes = VendConf.RoomListEditNotes;
                    }
                }

            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return data;
        }
        public RoomingListReportBData MasterRoomingListReport(int GroupID)
        {
            RoomingListReportBData data = new RoomingListReportBData();
            data.Rooms = new List<RoomingListReportRoomsBData>();

            try
            {
               data.Rooms = (from room in _context.tblGroupRooms
                                  from serv in _context.tblServices
                                  where room.GroupID == GroupID && serv.ServiceTypeID == 1 && room.ServiceID == serv.ServiceID
                                  orderby room.ServiceID, room.Names
                                  select new RoomingListReportRoomsBData()
                                  {
                                      Names = room.Names,
                                      ServiceID = room.ServiceID,
                                      ServiceName = serv.ServiceCode,
                                      Title = room.Title,
                                      NumOfPerson = room.NoOfPerson,
                                      Notes = room.Notes,
                                      //  NumOfPersonRoom = room.NoOfPersonRoom,
                                  }).ToList();
                
                var Grp = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
                var Company = _context.tblCompanies.Where(comp => comp.CompanyCode == "BONJOUR").SingleOrDefault();
                if (Company != null)
                {
                    data.CompanyName = Company.CompanyName;
                    data.CompanyInfo = Company.Address + ", " + Company.City + ", " + Company.StateID + ", " + Company.PostalCode + ", Phone " + Company.PhoneNumber + ", Fax " + Company.FaxNumber;
                }
                if (Grp != null)
                {
                    data.GroupName = Grp.GroupName;
                    data.GroupCode = Grp.GroupCode;
                    data.Nationality = _context.tblCountries.Where(ctr => ctr.CountryID == Grp.CountryID).SingleOrDefault().Nationality;
                    data.ArrivalDateTime = Grp.ArrivalDate != null ? Convert.ToDateTime(Grp.ArrivalDate).ToString("dd-MMM-yy"):null;
                    data.DepartureDateTime = Grp.DepartureDate != null ? Convert.ToDateTime(Grp.DepartureDate).ToString("dd-MMM-yy") : null;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return data;
        }
        public TechProgReportBData TechProgReport(int GroupID)
        {
            TechProgReportBData data = new TechProgReportBData();
            data.Rows = new List<TechProgReportRowsBData>();
            data.BusRows = new List<TechProgReportBusRowsBData>();
            data.GuideRows = new List<TechProgReportGuideRowsBData>();

            try
            {
                var Grp = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
                var Company = _context.tblCompanies.Where(comp => comp.CompanyCode == "BONJOUR").SingleOrDefault();
                if (Company != null)
                {
                    data.CompanyName = Company.CompanyName;
                    data.CompanyAddress = Company.Address;
                    data.CompanyCity = Company.City;
                    data.CompanyState = Company.StateID;
                    data.CompanyPostalCode = Company.PostalCode;
                    data.CompanyPhone = Company.PhoneNumber;
                    data.CompanyFax = Company.FaxNumber;
                }
                if (Grp != null)
                {
                    data.GroupName = Grp.GroupName;
                    data.GroupCode = Grp.GroupCode;
                    data.ArrivalDate = Grp.ArrivalDate;
                    data.DepartureDate = Grp.DepartureDate;
                }
                data.Rows = (from tdet in _context.tblTechDetails
                             where tdet.GroupID == GroupID && tdet.PrnFlg == true
                             orderby tdet.Date, tdet.Time
                             select new TechProgReportRowsBData()
                             {
                                 Date = tdet.Date,
                                 Time = tdet.Time,
                                 Description = tdet.Description,
                                 Via = tdet.Via,
                                 VendorID = tdet.VendorID,
                                 GuideID = tdet.GuideID,
                                 DetailID = tdet.DetailID,
                                 Type = tdet.Type,
                                 DetailDescription = _context.tblDetails.Any(det => det.Active == true && det.DetailID == tdet.DetailID) ? _context.tblDetails.Where(det => det.Active == true && det.DetailID == tdet.DetailID).FirstOrDefault().DetailDescription : null, 
                             }).ToList();
               
                var VendorIds = data.Rows.GroupBy(row => row.VendorID).Select(row => row.Key).ToList();
                var Vendors = _context.tblVendors.Where(vendor => VendorIds.Contains(vendor.VendorID)).ToList();
                var GuideInfo = (from guide in _context.tblGuides
                                 from tdet in _context.tblTechDetails
                                 where guide.GuideID == tdet.GuideID && tdet.GroupID == GroupID
                                 orderby tdet.GuideID, tdet.Date
                                 select new 
                                 {
                                     GuideID = guide.GuideID,
                                     FirstName = guide.FirstName,
                                     LastName = guide.LastName,
                                     Sex = guide.Sex,
                                     Phone = guide.Phone,
                                     Notes = tdet.Description,
                                     StartDate = tdet.Date,
                                 }).ToList();

                var BusInfo = (from Vendor in _context.tblVendors
                               from tdet in _context.tblTechDetails
                               where Vendor.VendorID == tdet.VendorID && tdet.Type == "Bus" && tdet.GroupID == GroupID
                               orderby tdet.VendorID, tdet.Date
                               select new
                               {
                                   VendorTypeID = Vendor.VendorTypeID,
                                   VendorID = Vendor.VendorID,
                                   BusName = Vendor.VendorName,
                                   Phone = Vendor.PhoneNumber,
                                   Notes = tdet.Description,
                                   StartDate = tdet.Date,
                               }).ToList();
                var RegionIds = Vendors.GroupBy(Vnd=>Vnd.RegionID).Select(Vnd=>Vnd.Key).ToList();
                var Areas = _context.tblAreas.Where(area=>RegionIds.Contains((short)area.RegionID)).ToList();
                                              
                foreach(var row in data.Rows)
                {
                    if(row.GuideID != null)
                    {
                        var Guide = GuideInfo.Where(guide => guide.GuideID == row.GuideID).FirstOrDefault();
                        row.FirstName = Guide.FirstName;
                        row.LastName = Guide.LastName;
                        row.Sex = Guide.Sex;
                    }
                    if(row.VendorID != null)
                    {
                        var Vnd = Vendors.Where(vnd => vnd.VendorID == row.VendorID).SingleOrDefault();
                        if(Vnd != null)
                        {
                            row.VendorName = Vnd.VendorName;
                            row.VendorPhone = Vnd.PhoneNumber;
                            row.VendorState = Vnd.StateID;
                            row.VendorAddress = Vnd.Address;
                            row.Area = Vnd.VendorTypeID == 1 ? (Areas.Any(area=>area.AreaID == Vnd.AreaID)?Areas.Where(area=>area.AreaID == Vnd.AreaID).Select(area=>area.AreaDesc).SingleOrDefault():"") : "";
                        }
                    }
                }
                var guideindex = 0;
                for (int ind = 0; ind < GuideInfo.Count; ++ind)
                {
                    data.GuideRows.Add(new TechProgReportGuideRowsBData()
                    {
                        FinishDate = GuideInfo[ind].StartDate,
                        FirstName = GuideInfo[ind].FirstName,
                        LastName = GuideInfo[ind].LastName,
                        Sex = GuideInfo[ind].Sex,
                        StartDate = GuideInfo[ind].StartDate,
                        Phone = GuideInfo[ind].Phone,
                        Notes = GuideInfo[ind].Notes,
                    });
                    var GuideID = GuideInfo[ind].GuideID;
                    var date = GuideInfo[ind].StartDate;
                    for(int index = ind + 1; index < GuideInfo.Count; ++index)
                    {
                        if (GuideID != GuideInfo[index].GuideID)
                        {
                            data.GuideRows[guideindex].FinishDate = GuideInfo[index - 1].StartDate;
                            ind = index - 1;
                            break;
                        }
                        if((int)(Convert.ToDateTime(GuideInfo[index].StartDate) - Convert.ToDateTime(date)).TotalDays <= 1)
                        {
                            date = GuideInfo[index].StartDate;
                        }
                        else
                        {
                            data.GuideRows[guideindex].FinishDate = GuideInfo[index - 1].StartDate;
                            ind = index - 1;
                            break;
                        }
                        if(index == GuideInfo.Count - 1)
                        {
                            data.GuideRows[guideindex].FinishDate = GuideInfo[index].StartDate;
                            ind = index;
                        }
                    }
                    ++guideindex;
                }
                if (data.GuideRows.Count > 1)
                    data.GuideRows = data.GuideRows.OrderBy(grows => grows.StartDate).Select(grows => grows).ToList();
                var busindex = 0;
                for (int ind = 0; ind < BusInfo.Count; ++ind)
                {
                    data.BusRows.Add(new TechProgReportBusRowsBData()
                    {
                        FinishDate = BusInfo[ind].StartDate,
                        BusName = BusInfo[ind].BusName,
                        Phone = BusInfo[ind].Phone,
                        Notes = BusInfo[ind].Notes,
                        StartDate = BusInfo[ind].StartDate,
                    });
                    var VendorID = BusInfo[ind].VendorID;
                    var date = BusInfo[ind].StartDate;
                    for (int index = ind + 1; index < BusInfo.Count; ++index)
                    {
                        if (VendorID != BusInfo[index].VendorID)
                        {
                            data.BusRows[busindex].FinishDate = BusInfo[index - 1].StartDate;
                            ind = index - 1;
                            break;
                        }
                        if ((int)(Convert.ToDateTime(BusInfo[index].StartDate) - Convert.ToDateTime(date)).TotalDays <= 1)
                        {
                            date = BusInfo[index].StartDate;
                        }
                        else
                        {
                            data.BusRows[busindex].FinishDate = BusInfo[index - 1].StartDate;
                            ind = index - 1;
                            break;
                        }
                        if (index == BusInfo.Count - 1)
                        {
                            data.BusRows[busindex].FinishDate = BusInfo[index].StartDate;
                            ind = index;
                        }
                    }
                    ++busindex;
                }
                if (data.BusRows.Count > 1)
                    data.BusRows = data.BusRows.OrderBy(brows => brows.StartDate).Select(brows=>brows).ToList();
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return data;
        }
        public GVendorListReportBData GroupVendorListReport(int GroupID)
        {
            GVendorListReportBData data = new GVendorListReportBData();
            data.Hotels = new List<GVendorListReportHotelsBData>();
            var Grp = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
            var Cust = _context.tblCustomers.Where(cust => cust.CustomerID == Grp.CustomerID).SingleOrDefault();
            var Company = _context.tblCompanies.Where(comp => comp.CompanyCode == "BONJOUR").SingleOrDefault();
            if (Company != null)
            {
                data.CompanyName = Company.CompanyName;
                data.CompanyInfo = Company.Address + ", " + Company.City + ", " + Company.StateID + ", " + Company.PostalCode + ", Phone " + Company.PhoneNumber + ", Fax " + Company.FaxNumber;
            }
            if (Cust != null)
            {
                data.Customer = Cust.OrganizationName;
                data.CustomerContactName = Cust.ContactName;
                data.CustomerPhone = Cust.PhoneNumber1;
                data.CustomerFax = Cust.FaxNumber;
                data.CustomerEmail = Cust.ContactEMail;
            }
            data.RoomCount = Grp.Rooms;
            data.GroupName = Grp.GroupName;
            data.GroupID = Grp.GroupID;
            var Emp = _context.tblEmployees.Where(emp => emp.EmployeeID == Grp.EmployeeID).SingleOrDefault();
            if (Emp != null)
                data.EmpName = Emp.FirstName + " " + Emp.LastName;
            data.ArrivalDate = Grp.ArrivalDate;
            data.DepartureDate = Grp.DepartureDate;
            data.Hotels = (from VConf in _context.tblVendorConfirms
                           from Vnd in _context.tblVendors
                           where VConf.GroupID == GroupID && Vnd.VendorID == VConf.VendorID && VConf.StatusID == 2
                           select new GVendorListReportHotelsBData
                           {
                               ArrivalDate = VConf.ArrivalDate,
                               DepartureDate = VConf.DepartureDate,
                               HotelAddress = Vnd.Address + ", " + Vnd.City + " " + Vnd.StateID + ", " + Vnd.PostalCode,
                               HotelCity = Vnd.City,
                               HotelFax = Vnd.FaxSales,
                               HotelName = Vnd.VendorName,
                               HotelPhone = Vnd.PhoneSales,
                           }).ToList();
            return data;
        }
        public GroupInvoiceReportBData GroupInvoiceReport(int GroupID)
        {
            string Country = "";
            GroupInvoiceReportBData InvData = new GroupInvoiceReportBData();
            InvData.Invoices = new List<GroupInvoiceReportRowBData>();
            var Group = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
            var Company = _context.tblCompanies.Where(comp => comp.CompanyCode == "BONJOUR").SingleOrDefault();
            var Cust = _context.tblCustomers.Where(cust => cust.CustomerID == Group.CustomerID).SingleOrDefault();
            var CustCont = _context.tblCustomerContacts.Where(cnt => cnt.CCID == Group.CCID).SingleOrDefault();

            if (Company != null)
            {
                InvData.CompanyName = Company.CompanyName;
                InvData.CompanyAddress = Company.Address + ", " + Company.City + ", " + Company.StateID + ", " + Company.PostalCode;
                InvData.CompanyPhone = Company.PhoneNumber;
                InvData.CompanyFax = Company.FaxNumber;
                InvData.CompanyFedID = Company.FedID;
            }
            if (Cust.CountryID != null)
                Country = _context.tblCountries.Where(ctr => ctr.CountryID == Cust.CountryID).SingleOrDefault().CountryName;
            if (Cust != null)
            {
                InvData.Customer = Cust.OrganizationName;
                InvData.CustomerFax = Cust.FaxNumber;
                InvData.CustomerEmail = Cust.ContactEMail;
                InvData.CustomerAddress = Cust.Address + ", " + Cust.City + ", " + Cust.PostalCode + ", " + Country;
            }
            if (CustCont != null)
            {
                InvData.Contact = CustCont.Name;
                InvData.ContactPhone = CustCont.Phone;
                InvData.ContactFax = CustCont.Fax;
                InvData.ContactEmail = CustCont.EMail;
                InvData.ContactTitle = CustCont.Title;
            }
            if (Group != null)
            {
                InvData.InvTotalDeposit = _context.tblDeposits.Where(dep => dep.GroupID == Group.GroupID).Sum(dep => dep.Received);
                InvData.InvTotalRefund = _context.tblDeposits.Where(dep => dep.GroupID == Group.GroupID).Sum(dep => dep.Refunded);
                var Bank = _context.tblBanks.Where(bank => bank.BankID == Group.BankID).SingleOrDefault();
                InvData.InvComm = Group.InvComm;
                InvData.ProInvComm = Group.ProComm;
                InvData.ProInv = Group.ProInv;
                InvData.InvoiceNo = Group.GroupCode;
                InvData.InvoiceDate = Group.InvDate;
                InvData.GroupName = Group.GroupName;
                InvData.ArrivalDate = Group.ArrivalDate;
                InvData.PaymTerms = Group.PayTerms;
                InvData.PaymentDueDate = Group.DepositDue;
                InvData.FinalPayment = Group.FinalPayment;
                InvData.DepositM = Group.DepositM;
                InvData.DepositAmt = Group.DepositAmt;
                if (Bank != null)
                {
                    InvData.PayBankName = Bank.BankName;
                    InvData.PayAddress = Bank.Address + ", " + Bank.City + ", " + Bank.StateID + ", " + Bank.PostalCode;
                    InvData.PayAccNo = Bank.AccNo;
                    InvData.PayAbaNo = Bank.ABANo;
                    InvData.PaySwiftCode = Bank.SwiftCode;
                }
                if (Group.InvEmployeeID != null && _context.tblEmployees.Any(emp => emp.EmployeeID == Group.InvEmployeeID))
                {
                    var Emp = _context.tblEmployees.Where(emp => emp.EmployeeID == Group.InvEmployeeID).SingleOrDefault();
                    InvData.AgentName = Emp.FirstName + " " + Emp.LastName;
                }
            }
            InvData.Invoices = (from inv in _context.tblGroupInvoices
                                where inv.GroupID == GroupID
                                orderby inv.LineNo
                                select new GroupInvoiceReportRowBData()
                                {
                                    Qty = inv.Quantity,
                                    Price = inv.PriceChange,
                                    Debit = inv.Debit,
                                    Credit = inv.Credit,
                                    Description = inv.Description,
                                    Curr = inv.Currency,
                                }).ToList();
            return InvData;
        }
        public GroupInvoiceReportBData GirGroupInvoiceReport(int GroupID, int CustomerID)
        {
            string Country = "";
            GroupInvoiceReportBData InvData = new GroupInvoiceReportBData();
            InvData.Invoices = new List<GroupInvoiceReportRowBData>();
            var GirGroupDet = _context.tblGirGroupDetails.Where(det => det.GroupID == GroupID && det.CustomerID == CustomerID).SingleOrDefault();
            var Grp = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
            var Company = _context.tblCompanies.Where(comp => comp.CompanyCode == "BONJOUR").SingleOrDefault();
            var Cust = _context.tblCustomers.Where(cust => cust.CustomerID == CustomerID).SingleOrDefault();
            var CustCont = _context.tblCustomerContacts.Where(cnt => cnt.CCID == GirGroupDet.CCID).SingleOrDefault();

            if (Company != null)
            {
                InvData.CompanyName = Company.CompanyName;
                InvData.CompanyAddress = Company.Address + ", " + Company.City + ", " + Company.StateID + ", " + Company.PostalCode;
                InvData.CompanyPhone = Company.PhoneNumber;
                InvData.CompanyFax = Company.FaxNumber;
                InvData.CompanyFedID = Company.FedID;
            }
            if (Cust.CountryID != null)
                Country = _context.tblCountries.Where(ctr => ctr.CountryID == Cust.CountryID).SingleOrDefault().CountryName;
            if (Cust != null)
            {
                InvData.Customer = Cust.OrganizationName;
                InvData.CustomerFax = Cust.FaxNumber;
                InvData.CustomerEmail = Cust.ContactEMail;
                InvData.CustomerAddress = Cust.Address + ", " + Cust.City + ", " + Cust.PostalCode + ", " + Country;
            }
            if (CustCont != null)
            {
                InvData.Contact = CustCont.Name;
                InvData.ContactPhone = CustCont.Phone;
                InvData.ContactFax = CustCont.Fax;
                InvData.ContactEmail = CustCont.EMail;
                InvData.ContactTitle = CustCont.Title;
            }
            if (GirGroupDet != null && Grp != null)
            {
                InvData.InvTotalDeposit = _context.tblDeposits.Where(dep => dep.GroupID == GroupID).Sum(dep => dep.Received);
                InvData.InvTotalRefund = _context.tblDeposits.Where(dep => dep.GroupID == GroupID).Sum(dep => dep.Refunded);
                var Bank = _context.tblBanks.Where(bank => bank.BankID == GirGroupDet.BankID).SingleOrDefault();
                InvData.InvComm = GirGroupDet.InvComm;
                InvData.ProInvComm = GirGroupDet.ProComm;
                InvData.ProInv = GirGroupDet.ProInv;
                InvData.InvoiceNo = Grp.GroupCode;
                InvData.InvoiceDate = GirGroupDet.InvDate;
                InvData.GroupName = Grp.GroupName;
                InvData.ArrivalDate = Grp.ArrivalDate;
                InvData.PaymTerms = GirGroupDet.PayTerms;
                InvData.PaymentDueDate = GirGroupDet.DepositDue;
                InvData.FinalPayment = GirGroupDet.FinalPayment;
                InvData.DepositM = GirGroupDet.DepositM;
                InvData.DepositAmt = GirGroupDet.DepositAmt;
                if (Bank != null)
                {
                    InvData.PayBankName = Bank.BankName;
                    InvData.PayAddress = Bank.Address + ", " + Bank.City + ", " + Bank.StateID + ", " + Bank.PostalCode;
                    InvData.PayAccNo = Bank.AccNo;
                    InvData.PayAbaNo = Bank.ABANo;
                    InvData.PaySwiftCode = Bank.SwiftCode;
                }
                if (GirGroupDet.InvEmployeeID != null && _context.tblEmployees.Any(emp => emp.EmployeeID == GirGroupDet.InvEmployeeID))
                {
                    var Emp = _context.tblEmployees.Where(emp => emp.EmployeeID == GirGroupDet.InvEmployeeID).SingleOrDefault();
                    InvData.AgentName = Emp.FirstName + " " + Emp.LastName;
                }

            }
            InvData.Invoices = (from inv in _context.tblGirGroupInvoices
                                where inv.GroupID == GroupID && inv.CustomerID == CustomerID
                                orderby inv.LineNo
                                select new GroupInvoiceReportRowBData()
                                {
                                    Qty = inv.Quantity,
                                    Price = inv.PriceChange,
                                    Debit = inv.Debit,
                                    Credit = inv.Credit,
                                    Description = inv.Description,
                                    Curr = inv.Currency,
                                }).ToList();
            return InvData;
        }
        public CustomerInvoiceReportBData CustomerInvoiceReport(int CustomerID, DateTime StartDate, DateTime EndDate)
        {
            CustomerInvoiceReportBData Data = new CustomerInvoiceReportBData();
            Data.CustomerDetail = (from cust in _context.tblCustomers
                                   where cust.CustomerID == CustomerID
                                   select new CustomerBData
                                   {
                                       CustomerCode = cust.CustomerCode,
                                       OrganizationName = cust.OrganizationName,
                                   }).SingleOrDefault();
            var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => ggrp.CustomerID == CustomerID && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
            Data.Invoices = (from grp in _context.tblGroups
                             where (grp.CustomerID == CustomerID || GirGroupIDs.Contains(grp.GroupID))
                             && grp.ArrivalDate >= StartDate
                             && grp.ArrivalDate <= EndDate
                             //&& grp.Cancelled == false
                             && grp.GroupID != 0
                             && grp.FinalPayment != null
                             orderby grp.FinalPayment
                             select new CustomerInvoiceReportRowBData
                             {
                                 GroupCode = grp.GroupCode,
                                 GroupName = grp.GroupName,
                                 ArrivalDate = grp.ArrivalDate,
                                 DepositDue = grp.DepositDue,
                                 FinalDue = grp.FinalPayment,
                                 TotalPaid = (from Deposits in _context.tblDeposits
                                              where grp.GroupID == Deposits.GroupID
                                              && Deposits.Received != null
                                              && Deposits.GroupID != 0
                                              select Deposits.Received).Sum(),
                                 TotalInvoice = grp.IsGir == true ? (from GirGroupInvoice in _context.tblGirGroupInvoices
                                                                     where grp.GroupID == GirGroupInvoice.GroupID
                                                                     && GirGroupInvoice.Credit != null
                                                                     && GirGroupInvoice.Debit != null
                                                                     && GirGroupInvoice.GroupID != 0
                                                                     select (GirGroupInvoice.Credit - GirGroupInvoice.Debit)).Sum()
                                                                  : (from GroupInvoice in _context.tblGroupInvoices
                                                                      where grp.GroupID == GroupInvoice.GroupID
                                                                      && GroupInvoice.Credit != null
                                                                      && GroupInvoice.Debit != null
                                                                      && GroupInvoice.GroupID != 0
                                                                      select (GroupInvoice.Credit - GroupInvoice.Debit)).Sum(),
                                 Deposit = grp.IsGir == true ? ((bool)grp.DepositM ? ((from GirGroupInvoice in _context.tblGirGroupInvoices
                                                                                       where grp.GroupID == GirGroupInvoice.GroupID
                                                                                        && grp.DepositAmt != null
                                                                                        && GirGroupInvoice.GroupID != 0
                                                                                        select grp.DepositAmt).Sum())
                                                                                    : (((from GirGroupInvoice in _context.tblGirGroupInvoices
                                                                                         where grp.GroupID == GirGroupInvoice.GroupID
                                                                                         && GirGroupInvoice.Credit != null
                                                                                         && GirGroupInvoice.Debit != null
                                                                                         && GirGroupInvoice.GroupID != 0
                                                                                         select (GirGroupInvoice.Debit - GirGroupInvoice.Credit)).Sum()) * (decimal?)0.2))
                                                            : ((bool)grp.DepositM ? ((from GroupInvoice in _context.tblGroupInvoices
                                                                                       where grp.GroupID == GroupInvoice.GroupID
                                                                                       && grp.DepositAmt != null
                                                                                       && GroupInvoice.GroupID != 0
                                                                                       select grp.DepositAmt).Sum())
                                                                                   : (((from GroupInvoice in _context.tblGroupInvoices
                                                                                        where grp.GroupID == GroupInvoice.GroupID
                                                                                        && GroupInvoice.Credit != null
                                                                                        && GroupInvoice.Debit != null
                                                                                        && GroupInvoice.GroupID != 0
                                                                                        select (GroupInvoice.Debit - GroupInvoice.Credit)).Sum()) * (decimal?)0.2)),
                                 Balance = -1 * (((from deposits in _context.tblDeposits
                                                   where grp.GroupID == deposits.GroupID
                                                   && deposits.Received != null
                                                   && deposits.GroupID != 0
                                                   select deposits.Received).Sum())
                                                            +
                                            (grp.IsGir == true ? ((from GirGroupInvoice in _context.tblGirGroupInvoices
                                                                   where grp.GroupID == GirGroupInvoice.GroupID
                                                                   && GirGroupInvoice.Credit != null
                                                                   && GirGroupInvoice.Debit != null
                                                                   && GirGroupInvoice.GroupID != 0
                                                                   select (GirGroupInvoice.Credit - GirGroupInvoice.Debit)).Sum())
                                                              : ((from GroupInvoice in _context.tblGroupInvoices
                                                                  where grp.GroupID == GroupInvoice.GroupID
                                                                  && GroupInvoice.Credit != null
                                                                  && GroupInvoice.Debit != null
                                                                  && GroupInvoice.GroupID != 0
                                                                  select (GroupInvoice.Credit - GroupInvoice.Debit)).Sum()))),
                             }).ToList();
            if (Data.CustomerDetail == null)
                Data.CustomerDetail = new CustomerBData();
            if (!Data.Invoices.Any())
                Data.Invoices = new List<CustomerInvoiceReportRowBData>();
            return Data;
        }
        public FrmCustomerGroupRevenueReportBData LoadFrmCountryCustomerGroupRevenueReport()
        {
            FrmCustomerGroupRevenueReportBData CustomerGroupRevenueReport = new FrmCustomerGroupRevenueReportBData();
            CustomerGroupRevenueReport.Nationalities = new Dictionary<int, string>();
            CustomerGroupRevenueReport.Nationalities = (from country in _context.tblCountries
                                                        where country.Active == true
                                                        orderby country.Nationality
                                                        select new
                                                        {
                                                            Key = country.CountryID,
                                                            Value = country.Nationality,
                                                        }).ToDictionary(o => o.Key, o => o.Value);
            return CustomerGroupRevenueReport;
        }
        public CountryCustomerGroupRevenueReportBData CountryCustomerGroupRevenueReport(int NationalityID, DateTime StartDate, DateTime EndDate)
        {
            CountryCustomerGroupRevenueReportBData Data = new CountryCustomerGroupRevenueReportBData();
            Data.Nationality = (from cnt in _context.tblCountries
                                where cnt.CountryID == NationalityID
                                select cnt.Nationality).SingleOrDefault();
           
            Data.Revenues = (from grp in _context.tblGroups
                             from cust in _context.tblCustomers
                             where cust.Active == true
                             && cust.CountryID == NationalityID
                             && grp.CustomerID == cust.CustomerID
                             && grp.ArrivalDate >= StartDate
                             && grp.ArrivalDate <= EndDate
                             && grp.GroupID != 0
                             //&& grp.Cancelled == false
                             select new CountryCustomerGroupRevenueReportRowBData
                             {
                                 CustomerID = cust.CustomerID,
                                 Customer = cust.CustomerCode,
                                 CustomerName = cust.OrganizationName,
                             }).Distinct().OrderBy(x=>x.Customer).ToList();
            var GirRevenue = (from Girgrp in _context.tblGirGroupDetails
                              from grp in _context.tblGroups
                              from cust in _context.tblCustomers
                              where Girgrp.CustomerID == cust.CustomerID
                              && cust.Active == true
                              && Girgrp.GroupID == grp.GroupID
                              && grp.ArrivalDate >= StartDate
                              && grp.ArrivalDate <= EndDate
                              //&& grp.Cancelled == false
                              select new CountryCustomerGroupRevenueReportRowBData
                              {
                                  CustomerID = cust.CustomerID,
                                  Customer = cust.CustomerCode,
                                  CustomerName = cust.OrganizationName,
                              }).Distinct().OrderBy(x => x.Customer).ToList();
            var NonGirCustomerIDs = Data.Revenues.Select(cust => cust.CustomerID);
            Data.Revenues.AddRange(GirRevenue.Where(cust => !(NonGirCustomerIDs.Contains(cust.CustomerID))));
            var CustomerIDs = Data.Revenues.Select(cust => cust.CustomerID).ToList();
            var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => CustomerIDs.Contains(ggrp.CustomerID) && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
            var GroupIDs = _context.tblGroups.Where(grp => NonGirCustomerIDs.Contains(grp.CustomerID) || GirGroupIDs.Contains(grp.GroupID)).Select(grp => grp.GroupID).ToList();
            var Deposits = _context.tblDeposits.Where(dep => GroupIDs.Contains(dep.GroupID));
            var GirGroupInvoices = _context.tblGirGroupInvoices.Where(ggrp => GirGroupIDs.Contains(ggrp.GroupID));
            var GroupInvoices = _context.tblGroupInvoices.Where(grp => GroupIDs.Contains(grp.GroupID));
            var Groups = _context.tblGroups.Where(grp => NonGirCustomerIDs.Contains(grp.CustomerID));
            var GirGroups = _context.tblGroups.Where(grp => GirGroupIDs.Contains(grp.GroupID));
            foreach (var other in Data.Revenues)
            {
                other.GroupID = (from grp in _context.tblGroups where grp.CustomerID == other.CustomerID && grp.GroupID != 0 select grp.GroupID).Count() + (from ggrp in _context.tblGirGroupDetails where ggrp.CustomerID == other.CustomerID && ggrp.GroupID != 0 select ggrp.GroupID).Count();
                var DepositSum = (from grp in Groups
                                  from Dep in Deposits
                                  where grp.CustomerID == other.CustomerID
                                  && grp.GroupID == Dep.GroupID
                                  && Dep.Received != null
                                  && Dep.GroupID != 0
                                  select Dep.Received).Sum();
                var GirDepositSum=(from ggrp in GirGroups
                                   from Dep in Deposits
                                   where ggrp.CustomerID == other.CustomerID
                                   && ggrp.GroupID == Dep.GroupID
                                   && Dep.Received != null
                                   && Dep.GroupID != 0
                                   select Dep.Received).Sum();
                var InvoiceSum = (from grp in Groups
                                  from GroupInvoice in GroupInvoices
                                  where grp.CustomerID == other.CustomerID
                                  && grp.GroupID == GroupInvoice.GroupID
                                  && GroupInvoice.Credit != null
                                  && GroupInvoice.Debit != null
                                  && GroupInvoice.GroupID != 0
                                  select (GroupInvoice.Credit - GroupInvoice.Debit)).Sum();
                var GirInvoiceSum = (from ggrp in GirGroups
                                     from GirGroupInvoice in GirGroupInvoices
                                     where ggrp.CustomerID == other.CustomerID
                                     && ggrp.GroupID == GirGroupInvoice.GroupID
                                     && GirGroupInvoice.Credit != null
                                     && GirGroupInvoice.Debit != null
                                     && GirGroupInvoice.GroupID != 0
                                     select (GirGroupInvoice.Credit - GirGroupInvoice.Debit)).Sum();
                other.TotalPaid = (DepositSum != null ? DepositSum : 0) + (GirDepositSum != null ? GirDepositSum : 0);
                other.TotalInvoice = (InvoiceSum != null ? InvoiceSum : 0) + (GirInvoiceSum != null ? GirInvoiceSum : 0);
                other.Balance = -1 * (other.TotalPaid + other.TotalInvoice);
            }
            if (!Data.Revenues.Any())
                Data.Revenues = new List<CountryCustomerGroupRevenueReportRowBData>();
            return Data;
        }
        public CustomerPaymentReportBData CustomerPaymentReport(int CustomerID, DateTime StartDate, DateTime EndDate)
        {
            CustomerPaymentReportBData Data = new CustomerPaymentReportBData();
            Data.CustomerPaymentDetail = (from cust in _context.tblCustomers
                                          where cust.CustomerID == CustomerID
                                          select new CustomerPaymentBData
                                          {
                                              CustomerCode = cust.CustomerCode,
                                              OrganizationName = cust.OrganizationName,
                                          }).SingleOrDefault();
            Data.Payments = (from pay in _context.tblPayments
                             from paytype in _context.tblPayTypes
                             from dep in _context.tblDeposits
                             where pay.CustomerID == CustomerID
                             && dep.LineNo == pay.LineNo
                             && dep.CustomerID == CustomerID
                             && pay.PaymentDate >= StartDate
                             && pay.PaymentDate <= EndDate
                             && pay.PayTypeID == paytype.PayTypeID
                             && dep.GroupID != 0
                             select new CustomerPaymentReportRowBData
                             {
                                 LineNo = dep.LineNo,
                                 PaymentDate = pay.PaymentDate,
                                 Description = pay.Description,
                                 PayTypeCode = paytype.PayTypeCode,
                                 Payment = pay.Credit - pay.Debit,
                                 Refunded = pay.RefundF ? (from depos in _context.tblDeposits
                                                           where depos.LineNo == pay.LineNo
                                                           && depos.CustomerID == CustomerID
                                                           select dep.Refunded).FirstOrDefault() : null,
                             }).Distinct().OrderBy(x => x.PaymentDate).ToList();
            var Deposits = _context.tblDeposits.Where(dep => dep.CustomerID == CustomerID && dep.GroupID > 0).ToList();
            var DepositGroupIDs = Deposits.Select(dep => dep.GroupID).ToList();
            var Groups = _context.tblGroups.Where(grp => DepositGroupIDs.Contains(grp.GroupID)).ToList();
            foreach (var row in Data.Payments)
            {
                row.Rows = (from dep in Deposits
                            from grp in Groups
                            where dep.LineNo == row.LineNo
                            && dep.GroupID == grp.GroupID
                            select new CustomerPaymentOtherReportRowBData
                            {
                                GroupCode = grp.GroupCode,
                                GroupName = grp.GroupName,
                                Balance = Deposits.Where(depos=>depos.GroupID == dep.GroupID && depos.LineNo == row.LineNo && depos.Received != null).Select(depos=>depos.Received).Sum()
                                          - Deposits.Where(depos => depos.GroupID == dep.GroupID && depos.LineNo == row.LineNo && depos.Refunded != null).Select(depos => depos.Refunded).Sum(),
                            }).ToList();
            }
            if (Data.CustomerPaymentDetail == null)
                Data.CustomerPaymentDetail = new CustomerPaymentBData();
            if (!Data.Payments.Any())
                Data.Payments = new List<CustomerPaymentReportRowBData>();
            return Data;
        }
        public CustomerGroupReportBData CustomerGroupReport(int CustomerID, DateTime StartDate, DateTime EndDate, bool? IsGir)
        {
            CustomerGroupReportBData Data = new CustomerGroupReportBData();
            Data.CustomerDetail = (from cust in _context.tblCustomers
                                   where cust.CustomerID == CustomerID
                                   select new CustomerGroupBData
                                   {
                                       CustomerCode = cust.CustomerCode,
                                       OrganizationName = cust.OrganizationName,
                                   }).SingleOrDefault();
            switch (IsGir)
            {
                case null:
                    {
                        var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => ggrp.CustomerID == CustomerID && ggrp.GroupID != null && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
                        Data.Groups = (from grp in _context.tblGroups
                                       where (grp.CustomerID == CustomerID || GirGroupIDs.Contains(grp.GroupID))
                                       && grp.ArrivalDate >= StartDate
                                       && grp.ArrivalDate <= EndDate
                                           //&& grp.Cancelled == false
                                       && grp.GroupID != 0
                                       orderby grp.ArrivalDate
                                       select new CustomerGroupReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                       }).ToList();
                    }
                    break;
                case false:
                    {
                        Data.Groups = (from grp in _context.tblGroups
                                       where grp.CustomerID == CustomerID
                                       && grp.ArrivalDate >= StartDate
                                       && grp.ArrivalDate <= EndDate
                                           //&& grp.Cancelled == false
                                       && grp.GroupID != 0
                                       orderby grp.ArrivalDate
                                       select new CustomerGroupReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                       }).ToList();
                    }
                    break;
                case true:
                    {
                        var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => ggrp.CustomerID == CustomerID && ggrp.GroupID != null && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
                        Data.Groups = (from grp in _context.tblGroups
                                       where GirGroupIDs.Contains(grp.GroupID)
                                       && grp.ArrivalDate >= StartDate
                                       && grp.ArrivalDate <= EndDate
                                           //&& grp.Cancelled == false
                                       && grp.GroupID != 0
                                       orderby grp.ArrivalDate
                                       select new CustomerGroupReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                       }).ToList();
                    }
                    break;
                default:
                    {
                        var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => ggrp.CustomerID == CustomerID && ggrp.GroupID != null && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
                        Data.Groups = (from grp in _context.tblGroups
                                       where (grp.CustomerID == CustomerID || GirGroupIDs.Contains(grp.GroupID))
                                       && grp.ArrivalDate >= StartDate
                                       && grp.ArrivalDate <= EndDate
                                           //&& grp.Cancelled == false
                                       && grp.GroupID != 0
                                       orderby grp.ArrivalDate
                                       select new CustomerGroupReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                       }).ToList();
                    }
                    break;

            }
            if (Data.CustomerDetail == null)
                Data.CustomerDetail = new CustomerGroupBData();
            if (!Data.Groups.Any())
                Data.Groups = new List<CustomerGroupReportRowBData>();
            return Data;
        }
        public GroupBreakDownReportBData GroupBreakDownReport(DateTime StartDate, DateTime EndDate, bool? IsGir)
        {
            GroupBreakDownReportBData Data = new GroupBreakDownReportBData();
            switch (IsGir)
            {
                case null:
                    {
                        Data.Groups = (from grp in _context.tblGroups
                                       where grp.ArrivalDate >= StartDate && grp.ArrivalDate <= EndDate && grp.GroupID != 0
                                       orderby grp.ArrivalDate
                                       select new GroupBreakDownReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                           AsGir = grp.AsGir,
                                           GirGroupDetails = (from GrpRoom in _context.tblGroupRooms
                                                              from Cust in _context.tblCustomers
                                                              where GrpRoom.GroupID == grp.GroupID && GrpRoom.CustomerID == Cust.CustomerID
                                                              select new 
                                                              {
                                                                  CustomerID = Cust.CustomerID,
                                                                  Customer = Cust.OrganizationName,
                                                                  NoOfPerson = GrpRoom.NoOfPerson,
                                                              }
                                                              ).GroupBy(det => new { det.CustomerID, det.Customer }).Select(detail => new GirGroupDetailBData { Customer = detail.Key.Customer, NoOfPerson = detail.Sum(det=>det.NoOfPerson) }).ToList(),
                                       }).ToList();
                    }
                    break;
                case false:
                    {
                        Data.Groups = (from grp in _context.tblGroups
                                       where grp.ArrivalDate >= StartDate && grp.ArrivalDate <= EndDate && grp.GroupID != 0 && grp.IsGir == false
                                       orderby grp.ArrivalDate
                                       select new GroupBreakDownReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                           AsGir = grp.AsGir, 
                                       }).ToList();
                    }
                    break;
                case true:
                    {
                        Data.Groups = (from grp in _context.tblGroups
                                       where grp.ArrivalDate >= StartDate && grp.ArrivalDate <= EndDate && grp.GroupID != 0 && grp.IsGir == true
                                       orderby grp.ArrivalDate
                                       select new GroupBreakDownReportRowBData
                                       {
                                           GroupCode = grp.GroupCode,
                                           GroupName = grp.GroupName,
                                           ArrivalDate = grp.ArrivalDate,
                                           ArrivalCity = grp.ArrivalCity,
                                           DepartureDate = grp.DepartureDate,
                                           DepartureCity = grp.DepartureCity,
                                           NoOfPerson = grp.NoOfPerson,
                                           IsGir = grp.IsGir,
                                           AsGir = grp.AsGir,
                                           GirGroupDetails = (from GrpRoom in _context.tblGroupRooms
                                                              from Cust in _context.tblCustomers
                                                              where GrpRoom.GroupID == grp.GroupID && GrpRoom.CustomerID == Cust.CustomerID
                                                              select new
                                                              {
                                                                  CustomerID = Cust.CustomerID,
                                                                  Customer = Cust.OrganizationName,
                                                                  NoOfPerson = GrpRoom.NoOfPerson,
                                                              }
                                                              ).GroupBy(det => new { det.CustomerID, det.Customer }).Select(detail => new GirGroupDetailBData { Customer = detail.Key.Customer, NoOfPerson = detail.Sum(det => det.NoOfPerson) }).ToList(),
                                       }).ToList();
                    }
                    break;

            }
            return Data;
        }
        public VendorGroupReportBData VendorGroupReport(int VendorID, DateTime Lower, DateTime Upper, string TabName, List<int> StatusIDs, string City, bool? IsPlane, string Category, string CityDrop, bool? IsGir)
        {
            VendorGroupReportBData Data = new VendorGroupReportBData();
            var Groups = _context.tblGroups.AsQueryable();
            if (IsGir == true)
                Groups = Groups.Where(grp => grp.IsGir == true);
            if (IsGir == false)
                Groups = Groups.Where(grp => grp.IsGir != true);
            if (TabName == "Guide")
            {
                Data.VendorDetail = (from guide in _context.tblGuides
                                     where guide.GuideID == VendorID
                                     select new VendorGroupBData
                                     {
                                         VendorCode = guide.FirstName,
                                         VendorName = guide.LastName,
                                     }).SingleOrDefault();
            }
            else
            {
                Data.VendorDetail = (from ven in _context.tblVendors
                                     where ven.VendorID == VendorID
                                     select new VendorGroupBData
                                       {
                                           VendorCode = ven.VendorCode,
                                           VendorName = ven.VendorName,
                                       }).SingleOrDefault();
            }
            switch (TabName)
            {
                case "Hotel":
                    {
                        var VendorConfirms = _context.tblVendorConfirms.AsQueryable();
                        if (CityDrop != null && CityDrop != "")
                            VendorConfirms = VendorConfirms.Where(vcnf => vcnf.ArrivalCity == CityDrop);
                        Data.VenGroups = (from venconfirm in VendorConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          from VndCSt in _context.tblVConfirmStatus
                                          where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                          && venconfirm.ArrivalDate >= Lower
                                          && venconfirm.ArrivalDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          && StatusIDs.Contains((int)venconfirm.StatusID)
                                          //&& venconfirm.Selected == true
                                          //&& grp.Cancelled == false
                                          select new VendorGroupReportRowBData
                                          {
                                              VndConfStatus = VndCSt.StatusDesc,
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.ArrivalDate,
                                              ArrivalCity = venconfirm.ArrivalCity,
                                              NoOfPerson = grp.NoOfPerson,
                                              FileCreatedDate = grp.PushedDate,
                                          }).Distinct().OrderBy(row=>row.ArrivalDate).ToList();
                    }
                    break;
                case "Attractions":
                    {
                        Data.VenGroups = (from venconfirm in _context.tblAttractionsConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          from VndCSt in _context.tblVConfirmStatus
                                          where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                          && venconfirm.StartDate >= Lower
                                          && venconfirm.StartDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          && StatusIDs.Contains((int)venconfirm.StatusID)
                                          //&& venconfirm.Selected == true
                                          //&& grp.Cancelled == false
                                          select new VendorGroupReportRowBData
                                          {
                                              VndConfStatus = VndCSt.StatusDesc,
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.StartDate,
                                              NoOfPerson = grp.NoOfPerson,
                                              EmployeeName = (venconfirm.EmployeeID != null && venconfirm.EmployeeID != 0 && _context.tblEmployees.Any(emp => emp.EmployeeID == venconfirm.EmployeeID)) ? (from emp in _context.tblEmployees where venconfirm.EmployeeID == emp.EmployeeID select (emp.FirstName + " " + emp.LastName)).FirstOrDefault() : "",
                                              IsInvoice = venconfirm.IsInvoice,
                                              Optional = venconfirm.IsGuideInvoice,
                                          }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                    }
                    break;
                case "Restaurants":
                    {
                        var VendorConfirms = _context.tblRestaurantsConfirms.AsQueryable();
                        if (Category != null && Category != "")
                            VendorConfirms = VendorConfirms.Where(vcnf => vcnf.Category == Category);
                        Data.VenGroups = (from venconfirm in VendorConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          from VndCSt in _context.tblVConfirmStatus
                                          where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                          && venconfirm.StartDate >= Lower
                                          && venconfirm.StartDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          && StatusIDs.Contains((int)venconfirm.StatusID)
                                          //&& venconfirm.Selected == true
                                          //&& grp.Cancelled == false
                                          select new VendorGroupReportRowBData
                                          {
                                              VndConfStatus = VndCSt.StatusDesc,
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.StartDate,
                                              Menu = _context.tblServices.Any(ser => ser.ServiceID == venconfirm.ServiceID) ? (from Service in _context.tblServices
                                                                                                                               from ServiceTypes in _context.tblServiceTypes
                                                                                                                               where Service.ServiceID == venconfirm.ServiceID
                                                                                                                               && Service.ServiceTypeID == ServiceTypes.ServiceTypeID
                                                                                                                               && Service.Active == true
                                                                                                                               && ServiceTypes.Active == true
                                                                                                                               orderby ServiceTypes.ServiceTypeCode
                                                                                                                               select ServiceTypes.ServiceTypeCode + " - " + Service.ServiceCode).FirstOrDefault() : "",
                                              NoOfPerson = grp.NoOfPerson,
                                              Category = venconfirm.Category,
                                              EmployeeName = (venconfirm.EmployeeID != null && venconfirm.EmployeeID != 0 && _context.tblEmployees.Any(emp => emp.EmployeeID == venconfirm.EmployeeID)) ? (from emp in _context.tblEmployees where venconfirm.EmployeeID == emp.EmployeeID select (emp.FirstName + " " + emp.LastName)).FirstOrDefault() : "",
                                              IsInvoice = venconfirm.IsInvoice,
                                              Optional = venconfirm.IsGuideInvoice,
                                          }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                    }
                    break;
                case "Bus":
                    {
                        var VendorConfirms = _context.tblBusConfirms.AsQueryable();
                        if (City != null && City != "")
                            VendorConfirms = VendorConfirms.Where(vcnf => vcnf.StartCity.Contains(City));
                        Data.VenGroups = (from venconfirm in VendorConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          from VndCSt in _context.tblVConfirmStatus
                                          where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                          && venconfirm.StartDate >= Lower
                                          && venconfirm.StartDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          && StatusIDs.Contains((int)venconfirm.StatusID)
                                          //&& venconfirm.Selected == true
                                          //&& grp.Cancelled == false
                                          select new VendorGroupReportRowBData
                                          {
                                              VndConfStatus = VndCSt.StatusDesc,
                                              BusCount = venconfirm.BusCount,
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.StartDate,
                                              StartCity = venconfirm.StartCity,
                                              EndCity = venconfirm.EndCity,
                                              NoOfPerson = grp.NoOfPerson,
                                              EstNoOfPerson = grp.NoOfPerson,
                                              ServiceDate = venconfirm.ServiceDate,
                                              Notes = venconfirm.Notes,
                                              ServiceDetail = (venconfirm.ServiceDetID != null && venconfirm.ServiceDetID != 0 && _context.tblDetails.Any(det => det.DetailID == venconfirm.ServiceDetID)) ? (from det in _context.tblDetails where det.DetailID == venconfirm.ServiceDetID select det.DetailCode).FirstOrDefault() : "",
                                          }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                    }
                    break;
                case "Guide":
                    {
                        Data.VenGroups = (from venconfirm in _context.tblGuideConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          where venconfirm.GuideID == VendorID
                                          && venconfirm.StartDate >= Lower
                                          && venconfirm.StartDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          select new VendorGroupReportRowBData
                                          {
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.StartDate,
                                              NoOfPerson = grp.NoOfPerson,
                                          }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                    }
                    break;
                case "Misc":
                    {
                        var VendorConfirms = _context.tblMiscConfirms.AsQueryable();
                        if (IsPlane != null)
                            VendorConfirms = VendorConfirms.Where(vcnf => vcnf.PlaneFlag == IsPlane);
                        Data.VenGroups = (from venconfirm in VendorConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          from VndCSt in _context.tblVConfirmStatus
                                          where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                          && venconfirm.StartDate >= Lower
                                          && venconfirm.StartDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          && StatusIDs.Contains((int)venconfirm.StatusID)
                                          //&& venconfirm.Selected == true
                                          //&& grp.Cancelled == false
                                          select new VendorGroupReportRowBData
                                          {
                                              VndConfStatus = VndCSt.StatusDesc,
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.StartDate,
                                              Plane = venconfirm.PlaneFlag,
                                              NoOfPerson = grp.NoOfPerson,
                                              EstNoOfPerson = grp.NoOfPerson,
                                              Notes = venconfirm.Notes,
                                          }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                    }
                    break;
                case "Group CheckList":
                    {
                        var VendorTypeID = _context.tblVendors.Where(vnd => vnd.VendorID == VendorID).Select(vnd => vnd.VendorTypeID).SingleOrDefault();
                        switch(VendorTypeID)
                        {
                            case 1:
                                {
                                    var VendorConfirms = _context.tblVendorConfirms.AsQueryable();
                                    if (CityDrop != null && CityDrop != "")
                                        VendorConfirms = VendorConfirms.Where(vcnf => vcnf.ArrivalCity == CityDrop);
                                    Data.VenGroups = (from venconfirm in VendorConfirms
                                                      from grp in Groups
                                                      from cust in _context.tblCustomers
                                                      from VndCSt in _context.tblVConfirmStatus
                                                      where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                                      && venconfirm.ArrivalDate >= Lower
                                                      && venconfirm.ArrivalDate <= Upper
                                                      && grp.GroupID == venconfirm.GroupID
                                                      && cust.CustomerID == grp.CustomerID
                                                      && venconfirm.GroupID != 0
                                                      && StatusIDs.Contains((int)venconfirm.StatusID)
                                                      //&& venconfirm.Selected == true
                                                      //&& grp.Cancelled == false
                                                      select new VendorGroupReportRowBData
                                                      {
                                                          VndConfStatus = VndCSt.StatusDesc,
                                                          CustomerName = cust.OrganizationName,
                                                          GroupCode = grp.GroupCode,
                                                          GroupName = grp.GroupName,
                                                          ArrivalDate = venconfirm.ArrivalDate,
                                                          NoOfPerson = grp.NoOfPerson,
                                                          FileCreatedDate = grp.PushedDate,
                                                          IsGir = grp.IsGir,
                                                      }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                                }
                                break;
                            case 2:
                                {
                                    var VendorConfirms = _context.tblRestaurantsConfirms.AsQueryable();
                                    if (Category != null && Category != "")
                                        VendorConfirms = VendorConfirms.Where(vcnf => vcnf.Category == Category);
                                    Data.VenGroups = (from venconfirm in VendorConfirms
                                                      from grp in Groups
                                                      from cust in _context.tblCustomers
                                                      from VndCSt in _context.tblVConfirmStatus
                                                      where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                                      && venconfirm.StartDate >= Lower
                                                      && venconfirm.StartDate <= Upper
                                                      && grp.GroupID == venconfirm.GroupID
                                                      && cust.CustomerID == grp.CustomerID
                                                      && venconfirm.GroupID != 0
                                                      && StatusIDs.Contains((int)venconfirm.StatusID)
                                                      //&& venconfirm.Selected == true
                                                      //&& grp.Cancelled == false
                                                      select new VendorGroupReportRowBData
                                                      {
                                                          VndConfStatus = VndCSt.StatusDesc,
                                                          CustomerName = cust.OrganizationName,
                                                          GroupCode = grp.GroupCode,
                                                          GroupName = grp.GroupName,
                                                          ArrivalDate = venconfirm.StartDate,
                                                          NoOfPerson = grp.NoOfPerson,
                                                          FileCreatedDate = grp.PushedDate,
                                                          IsGir = grp.IsGir,
                                                      }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                                }
                                break;
                            case 3:
                                {
                                    var VendorConfirms = _context.tblBusConfirms.AsQueryable();
                                    if (City != null && City != "")
                                        VendorConfirms = VendorConfirms.Where(vcnf => vcnf.StartCity.Contains(City));
                                    Data.VenGroups = (from venconfirm in VendorConfirms
                                                      from grp in Groups
                                                      from cust in _context.tblCustomers
                                                      from VndCSt in _context.tblVConfirmStatus
                                                      where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                                      && venconfirm.StartDate >= Lower
                                                      && venconfirm.StartDate <= Upper
                                                      && grp.GroupID == venconfirm.GroupID
                                                      && cust.CustomerID == grp.CustomerID
                                                      && venconfirm.GroupID != 0
                                                      && StatusIDs.Contains((int)venconfirm.StatusID)
                                                      //&& venconfirm.Selected == true
                                                      //&& grp.Cancelled == false
                                                      select new VendorGroupReportRowBData
                                                      {
                                                          VndConfStatus = VndCSt.StatusDesc,
                                                          CustomerName = cust.OrganizationName,
                                                          GroupCode = grp.GroupCode,
                                                          GroupName = grp.GroupName,
                                                          ArrivalDate = venconfirm.StartDate,
                                                          NoOfPerson = grp.NoOfPerson,
                                                          FileCreatedDate = grp.PushedDate,
                                                          IsGir = grp.IsGir,
                                                      }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                                }
                                break;
                            case 4:
                            case 5:
                                {
                                    Data.VenGroups = (from venconfirm in _context.tblAttractionsConfirms
                                                      from grp in Groups
                                                      from cust in _context.tblCustomers
                                                      from VndCSt in _context.tblVConfirmStatus
                                                      where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                                      && venconfirm.StartDate >= Lower
                                                      && venconfirm.StartDate <= Upper
                                                      && grp.GroupID == venconfirm.GroupID
                                                      && cust.CustomerID == grp.CustomerID
                                                      && venconfirm.GroupID != 0
                                                      && StatusIDs.Contains((int)venconfirm.StatusID)
                                                      //&& venconfirm.Selected == true
                                                      //&& grp.Cancelled == false
                                                      select new VendorGroupReportRowBData
                                                      {
                                                          VndConfStatus = VndCSt.StatusDesc,
                                                          CustomerName = cust.OrganizationName,
                                                          GroupCode = grp.GroupCode,
                                                          GroupName = grp.GroupName,
                                                          ArrivalDate = venconfirm.StartDate,
                                                          NoOfPerson = grp.NoOfPerson,
                                                          FileCreatedDate = grp.PushedDate,
                                                          IsGir = grp.IsGir,
                                                      }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                                }
                                break;
                            default:
                                {
                                    var VendorConfirms = _context.tblVendorConfirms.AsQueryable();
                                    if (CityDrop != null && CityDrop != "")
                                        VendorConfirms = VendorConfirms.Where(vcnf => vcnf.ArrivalCity == CityDrop);
                                    Data.VenGroups = (from venconfirm in VendorConfirms
                                                      from grp in Groups
                                                      from cust in _context.tblCustomers
                                                      from VndCSt in _context.tblVConfirmStatus
                                                      where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                                      && venconfirm.ArrivalDate >= Lower
                                                      && venconfirm.ArrivalDate <= Upper
                                                      && grp.GroupID == venconfirm.GroupID
                                                      && cust.CustomerID == grp.CustomerID
                                                      && venconfirm.GroupID != 0
                                                      && StatusIDs.Contains((int)venconfirm.StatusID)
                                                      //&& venconfirm.Selected == true
                                                      //&& grp.Cancelled == false
                                                      select new VendorGroupReportRowBData
                                                      {
                                                          VndConfStatus = VndCSt.StatusDesc,
                                                          CustomerName = cust.OrganizationName,
                                                          GroupCode = grp.GroupCode,
                                                          GroupName = grp.GroupName,
                                                          ArrivalDate = venconfirm.ArrivalDate,
                                                          NoOfPerson = grp.NoOfPerson,
                                                          FileCreatedDate = grp.PushedDate,
                                                          IsGir = grp.IsGir,
                                                      }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                                }
                                break;
                        }
                    }
                    break;
                default:
                    {
                        var VendorConfirms = _context.tblVendorConfirms.AsQueryable();
                        if (CityDrop != null && CityDrop != "")
                            VendorConfirms = VendorConfirms.Where(vcnf => vcnf.ArrivalCity.Contains(CityDrop));
                        Data.VenGroups = (from venconfirm in VendorConfirms
                                          from grp in Groups
                                          from cust in _context.tblCustomers
                                          from VndCSt in _context.tblVConfirmStatus
                                          where venconfirm.VendorID == VendorID && venconfirm.StatusID == VndCSt.StatusID
                                          && venconfirm.ArrivalDate >= Lower
                                          && venconfirm.ArrivalDate <= Upper
                                          && grp.GroupID == venconfirm.GroupID
                                          && cust.CustomerID == grp.CustomerID
                                          && venconfirm.GroupID != 0
                                          && StatusIDs.Contains((int)venconfirm.StatusID)
                                          //&& venconfirm.Selected == true
                                          //&& grp.Cancelled == false
                                          select new VendorGroupReportRowBData
                                          {
                                              VndConfStatus = VndCSt.StatusDesc,
                                              CustomerName = cust.OrganizationName,
                                              GroupCode = grp.GroupCode,
                                              GroupName = grp.GroupName,
                                              ArrivalDate = venconfirm.ArrivalDate,
                                              ArrivalCity = venconfirm.ArrivalCity,
                                              NoOfPerson = grp.NoOfPerson,
                                              FileCreatedDate = grp.PushedDate,
                                          }).Distinct().OrderBy(row => row.ArrivalDate).ToList();
                    }
                    break;
            }
           
            if (Data.VendorDetail == null)
                Data.VendorDetail = new VendorGroupBData();
            if (!Data.VenGroups.Any())
                Data.VenGroups = new List<VendorGroupReportRowBData>();
            return Data;
        }
        public GuideGroupReportBData GuideGroupReport(int GuideID, DateTime Lower, DateTime Upper, bool? IsGir)
        {
            GuideGroupReportBData Data = new GuideGroupReportBData();

            Data.GuideDetail = (from guide in _context.tblGuides
                                where guide.GuideID == GuideID
                                select new GuideGroupBData
                                 {
                                     FirstName = guide.FirstName,
                                     LastName = guide.LastName,
                                     Phone1 = guide.Phone,
                                     Phone2 = guide.Phone2,
                                     Fax = guide.Fax,
                                     Address = guide.Address,
                                     City = guide.City,
                                     State = guide.StateID,
                                     PostalCode = guide.PostalCode,
                                 }).SingleOrDefault();
            var Groups = _context.tblGroups.AsQueryable();
            if (IsGir == true)
                Groups = Groups.Where(grp => grp.IsGir == true);
            if (IsGir == false)
                Groups = Groups.Where(grp => grp.IsGir != true);
            Data.GuideGroups = (from guideconfirm in _context.tblGuideConfirms
                                from grp in Groups
                                where guideconfirm.GuideID == GuideID
                                && guideconfirm.StartDate >= Lower
                                && guideconfirm.StartDate <= Upper
                                && guideconfirm.GroupID != 0
                                && grp.GroupID == guideconfirm.GroupID
                                //&& guideconfirm.Selected == true
                                //&& grp.Cancelled == false
                                select new GuideGroupReportRowBData
                                {
                                    StartDate = guideconfirm.StartDate,
                                    FinishDate = guideconfirm.FinishDate,
                                    GroupCode = grp.GroupCode,
                                    GroupName = grp.GroupName,
                                    ArrivalDate = grp.ArrivalDate,
                                    ArrivalCity = grp.ArrivalCity,
                                    DepartureDate = grp.DepartureDate,
                                    DepartureCity = grp.DepartureCity,
                                    NoOfPerson = grp.NoOfPerson,
                                }).Distinct().OrderBy(row=>row.ArrivalDate).ToList();
            if (Data.GuideDetail == null)
                Data.GuideDetail = new GuideGroupBData();
            if (!Data.GuideGroups.Any())
                Data.GuideGroups = new List<GuideGroupReportRowBData>();
            return Data;
        }
        public GroupGuideReportBData GroupGuideReport(DateTime Lower, DateTime Upper, bool? IsGir)
        {
            GroupGuideReportBData Data = new GroupGuideReportBData();
            var Groups = _context.tblGroups.AsQueryable();
            if (IsGir == true)
                Groups = Groups.Where(grp => grp.IsGir == true);
            if (IsGir == false)
                Groups = Groups.Where(grp => grp.IsGir != true);
            Data.GroupGuides = (from grp in Groups
                                where grp.ArrivalDate >= Lower
                                && grp.ArrivalDate <= Upper
                                && grp.DepartureDate != null
                                select new GroupGuideReportRowBData
                                {
                                    GroupID = grp.GroupID,
                                    GroupCode = grp.GroupCode,
                                    ArrivalDate = grp.ArrivalDate,
                                    DepartureDate = grp.DepartureDate,
                                    NoOfPerson = grp.NoOfPerson,
                                    Cancelled = grp.Cancelled,
                                }).OrderBy(rows=>rows.ArrivalDate).ToList();
            var GroupIDs = Data.GroupGuides.Select(grp => grp.GroupID).ToList();
            var GuideConfirms = _context.tblGuideConfirms.Where(gconf => GroupIDs.Contains(gconf.GroupID)).ToList();
            var GuideIDs = GuideConfirms.Select(gconf => gconf.GuideID).ToList();
            var Guides = _context.tblGuides.Where(guide => GuideIDs.Contains(guide.GuideID)).ToList();
            foreach (var row in Data.GroupGuides)
            {
                row.Rows = (from guideconfirm in GuideConfirms
                            from gui in Guides
                            where row.GroupID == guideconfirm.GroupID
                            && row.GroupID != 0
                            && gui.GuideID == guideconfirm.GuideID
                            select new GuideRowBData
                            {
                                StartDate = guideconfirm.StartDate,
                                FinishDate = guideconfirm.FinishDate,
                                FirstName = gui.FirstName,
                                LastName = gui.LastName,
                            }).Distinct().OrderBy(rows=>rows.StartDate).ToList();
            }
            if (!Data.GroupGuides.Any())
                Data.GroupGuides = new List<GroupGuideReportRowBData>();
            return Data;
        }
        public FrmCustomerGroupBalanceReportBData LoadFrmCountryCustomerGroupBalanceReport()
        {
            FrmCustomerGroupBalanceReportBData CustomerGroupBalanceReport = new FrmCustomerGroupBalanceReportBData();
            CustomerGroupBalanceReport.Nationalities = new Dictionary<int, string>();
            CustomerGroupBalanceReport.Nationalities = (from country in _context.tblCountries
                                                        where country.Active == true
                                                        orderby country.Nationality
                                                        select new
                                                        {
                                                            Key = country.CountryID,
                                                            Value = country.Nationality,
                                                        }).ToDictionary(o => o.Key, o => o.Value);
            return CustomerGroupBalanceReport;
        }
        public FrmVendorGroupReportBData LoadFrmVCnfStatus()
        {
            FrmVendorGroupReportBData data = new FrmVendorGroupReportBData();
            data.Status = new Dictionary<int, string>();
            data.City = new Dictionary<int, string>();
            data.Status = (from vcnfstatus in _context.tblVConfirmStatus
                           where vcnfstatus.Active == true
                           orderby vcnfstatus.StatusOrder
                           select new
                           {
                               Key = vcnfstatus.StatusID,
                               Value = vcnfstatus.StatusDesc,
                           }).ToDictionary(o => o.Key, o => o.Value);
            // Arrival City init
            var City = _context.tblCities.Where(city => city.Active == true && (city.CountryID == 1 || city.CountryID == 7)).OrderBy(city => city.City).ToList();
            foreach (var city in City)
            {
                data.City.Add(city.CityID, city.City);
            }
            return data;
        }
        public CountryCustomerGroupBalanceReportBData CountryCustomerGroupBalanceReport(int NationalityID, DateTime StartDate, DateTime EndDate)
        {
            CountryCustomerGroupBalanceReportBData Data = new CountryCustomerGroupBalanceReportBData();
            Data.Nationality = (from cnt in _context.tblCountries
                                where cnt.CountryID == NationalityID
                                select cnt.Nationality).SingleOrDefault();
            
            Data.Balances = (from grp in _context.tblGroups
                             from cust in _context.tblCustomers
                             where grp.CustomerID == cust.CustomerID
                             && cust.Active == true
                             && grp.ArrivalDate >= StartDate
                             && grp.ArrivalDate <= EndDate
                             //&& grp.Cancelled == false
                             select new CountryCustomerGroupBalanceReportRowBData
                             {
                                 CustomerID = cust.CustomerID,
                                 Customer = cust.CustomerCode,
                                 CustomerName = cust.OrganizationName,
                             }).Distinct().OrderBy(x => x.Customer).ToList();
            var GirBalance = (from Girgrp in _context.tblGirGroupDetails
                              from grp in _context.tblGroups
                              from cust in _context.tblCustomers
                              where Girgrp.CustomerID == cust.CustomerID
                              && cust.Active == true
                              && Girgrp.GroupID == grp.GroupID
                              && grp.ArrivalDate >= StartDate
                              && grp.ArrivalDate <= EndDate
                              //&& grp.Cancelled == false
                              select new CountryCustomerGroupBalanceReportRowBData
                              {
                                  CustomerID = cust.CustomerID,
                                  Customer = cust.CustomerCode,
                                  CustomerName = cust.OrganizationName,
                              }).Distinct().OrderBy(x => x.Customer).ToList();

            var NonGirCustomerIDs = Data.Balances.Select(cust => cust.CustomerID);
            Data.Balances.AddRange(GirBalance.Where(cust => !(NonGirCustomerIDs.Contains(cust.CustomerID))));

            var CustomerIDs = Data.Balances.Select(cust => cust.CustomerID).ToList();
            var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => CustomerIDs.Contains(ggrp.CustomerID) && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
            var GroupIDs = _context.tblGroups.Where(grp => NonGirCustomerIDs.Contains(grp.CustomerID) || GirGroupIDs.Contains(grp.GroupID)).Select(grp => grp.GroupID).ToList();
            var Deposits = _context.tblDeposits.Where(dep => GroupIDs.Contains(dep.GroupID));
            var GirGroupInvoices = _context.tblGirGroupInvoices.Where(ggrp => GirGroupIDs.Contains(ggrp.GroupID));
            var GroupInvoices = _context.tblGroupInvoices.Where(grp => GroupIDs.Contains(grp.GroupID));
            var Groups = _context.tblGroups.Where(grp => NonGirCustomerIDs.Contains(grp.CustomerID) || GirGroupIDs.Contains(grp.GroupID));
            foreach (var other in Data.Balances)
            {
                other.Rows = (from grp in Groups
                              where (grp.CustomerID == other.CustomerID || GirGroupIDs.Contains(grp.GroupID))
                              && grp.ArrivalDate >= StartDate
                              && grp.ArrivalDate <= EndDate
                              select new CountryCustomerGroupBalanceOtherReportRowBData
                              {
                                  GroupCode = grp.GroupCode,
                                  GroupName = grp.GroupName,
                                  ArrivalDate = grp.ArrivalDate,
                                  FinalPayment = grp.FinalPayment,
                                  TotalPaid = (from Dep in Deposits
                                               where grp.GroupID == Dep.GroupID
                                               && Dep.Received != null
                                               && Dep.GroupID != 0
                                               select Dep.Received).Sum(),
                                  Balance = -1 * ((decimal)((from Dep in Deposits
                                                             where grp.GroupID == Dep.GroupID
                                                             && Dep.Received != null
                                                             && Dep.GroupID != 0
                                                             select Dep.Received).Sum())
                                                         +
                                            (decimal)(grp.IsGir == true ? (from GirGroupInvoice in GirGroupInvoices
                                                                           where grp.GroupID == GirGroupInvoice.GroupID
                                                                           && GirGroupInvoice.Credit != null
                                                                           && GirGroupInvoice.Debit != null
                                                                           && GirGroupInvoice.GroupID != 0
                                                                           select (GirGroupInvoice.Credit - GirGroupInvoice.Debit)).Sum()
                                                                        : (from GroupInvoice in GroupInvoices
                                                                           where grp.GroupID == GroupInvoice.GroupID
                                                                           && GroupInvoice.Credit != null
                                                                           && GroupInvoice.Debit != null
                                                                           && GroupInvoice.GroupID != 0
                                                                           select (GroupInvoice.Credit - GroupInvoice.Debit)).Sum())),
                              }).Distinct().ToList();
            }
            if (!Data.Balances.Any())
                Data.Balances = new List<CountryCustomerGroupBalanceReportRowBData>();
            return Data;
        }
        public GroupRegionRoomDayReportBData GroupRegionRoomDayReport(DateTime BeginningDate, DateTime EndingDate)
        {
            GroupRegionRoomDayReportBData Data = new GroupRegionRoomDayReportBData();
            Data.GroupRegionRoomDays = (from vconf in _context.tblVendorConfirms
                                        from vconfroom in _context.tblVConfirmRooms
                                        from vendor in _context.tblVendors
                                        from reg in _context.tblRegions
                                        where vconf.ArrivalDate >= BeginningDate
                                        && vconf.ArrivalDate <= EndingDate
                                        && vendor.VendorID == vconf.VendorID
                                        && vendor.RegionID == reg.RegionID
                                        && vconfroom.ArrivalDate == vconf.ArrivalDate
                                        && vconfroom.VendorID == vconf.VendorID
                                        && vconfroom.GroupID != 0
                                        && vconfroom.GroupID == vconf.GroupID
                                        && vconf.DepartureDate != null
                                        && vconfroom.NoOfPerson != null
                                        select new GroupRegionRoomDayReportRowBData
                                        {
                                            RegionID = reg.RegionID,
                                            RegionCode = reg.RegionCode,
                                            RegionName = reg.RegionDesc,
                                            ArrivalDate = vconf.ArrivalDate,
                                            DepartureDate = vconf.DepartureDate,
                                            NoOfPerson = vconfroom.NoOfPerson,
                                        }).Distinct().OrderBy(x => x.ArrivalDate).ToList();
            if (!Data.GroupRegionRoomDays.Any())
                Data.GroupRegionRoomDays = new List<GroupRegionRoomDayReportRowBData>();
            return Data;
        }
        public GroupCityRoomDayReportBData GroupCityRoomDayReport(DateTime BeginningDate, DateTime EndingDate)
        {
            GroupCityRoomDayReportBData Data = new GroupCityRoomDayReportBData();
            Data.GroupCityRoomDays = (from vconf in _context.tblVendorConfirms
                                      from vconfroom in _context.tblVConfirmRooms
                                      from vendor in _context.tblVendors
                                      from state in _context.tblStates
                                      where vconf.ArrivalDate >= BeginningDate
                                      && vconf.ArrivalDate <= EndingDate
                                      && vendor.VendorID == vconf.VendorID
                                      && vendor.StateID == state.StateID
                                      && vconfroom.ArrivalDate == vconf.ArrivalDate
                                      && vconfroom.VendorID == vconf.VendorID
                                      && vconfroom.GroupID != 0
                                      && vconfroom.GroupID == vconf.GroupID
                                      && vconf.DepartureDate != null
                                      && vconfroom.NoOfPerson != null
                                      select new GroupCityRoomDayReportRowBData
                                      {
                                          StateID = state.StateID,
                                          City = (vendor.City != null && vendor.City != "") ? (vendor.City).ToUpper() : "",
                                          ArrivalDate = vconf.ArrivalDate,
                                          DepartureDate = vconf.DepartureDate,
                                          NoOfPerson = vconfroom.NoOfPerson,
                                      }).Distinct().OrderBy(x=>x.ArrivalDate).ToList();
            if (!Data.GroupCityRoomDays.Any())
                Data.GroupCityRoomDays = new List<GroupCityRoomDayReportRowBData>();
            return Data;
        }
        public CustomerARDayesReportBData CustomerARDayesReport(int CustomerID, DateTime StartDate, DateTime EndDate)
        {
            CustomerARDayesReportBData Data = new CustomerARDayesReportBData();
            Data.CustomerDetail = (from cust in _context.tblCustomers
                                   where cust.CustomerID == CustomerID
                                   select new ARDayesCustomerBData
                                   {
                                       CustomerCode = cust.CustomerCode,
                                       OrganizationName = cust.OrganizationName,
                                   }).SingleOrDefault();
            var GirGroupIDs = _context.tblGirGroupDetails.Where(ggrp => ggrp.CustomerID == CustomerID && ggrp.GroupID > 0).Select(ggrp => ggrp.GroupID).ToList();
            Data.ARDayes = (from grp in _context.tblGroups
                            where (grp.CustomerID == CustomerID || GirGroupIDs.Contains(grp.GroupID))
                            && grp.ArrivalDate >= StartDate
                            && grp.ArrivalDate <= EndDate
                            //&& grp.Cancelled == false
                            && grp.GroupID != 0
                            && grp.FinalPayment != null
                            orderby grp.FinalPayment
                            select new CustomerARDayesReportRowBData
                            {
                                GroupCode = grp.GroupCode,
                                GroupName = grp.GroupName,
                                FinalDue = grp.FinalPayment,
                                ArrivalDate = grp.ArrivalDate,
                                Balance = (((from deposits in _context.tblDeposits
                                             where grp.GroupID == deposits.GroupID
                                             && deposits.Received != null
                                             && deposits.GroupID != 0
                                             select deposits.Received).Sum())
                                                        +
                                   (grp.IsGir == true ? ((from GirGroupInvoice in _context.tblGirGroupInvoices
                                                          where grp.GroupID == GirGroupInvoice.GroupID
                                                          && GirGroupInvoice.Credit != null
                                                          && GirGroupInvoice.Debit != null
                                                          && GirGroupInvoice.GroupID != 0
                                                          select (GirGroupInvoice.Credit - GirGroupInvoice.Debit)).Sum())
                                                     : ((from GroupInvoice in _context.tblGroupInvoices
                                                         where grp.GroupID == GroupInvoice.GroupID
                                                         && GroupInvoice.Credit != null
                                                         && GroupInvoice.Debit != null
                                                         && GroupInvoice.GroupID != 0
                                                         select (GroupInvoice.Credit - GroupInvoice.Debit)).Sum()))),
                            }).ToList();
            if (Data.CustomerDetail == null)
                Data.CustomerDetail = new ARDayesCustomerBData();
            if (!Data.ARDayes.Any())
                Data.ARDayes = new List<CustomerARDayesReportRowBData>();
            return Data;
        }
        public FrmCustomerPaymentTypeReportBData LoadFrmCustomerPaymentTypeReport()
        {
            FrmCustomerPaymentTypeReportBData FrmCustomerPaymentTypeReport = new FrmCustomerPaymentTypeReportBData();

            FrmCustomerPaymentTypeReport.PaymentTypes = new Dictionary<int, string>();
            FrmCustomerPaymentTypeReport.PaymentTypes = (from ptype in _context.tblPayTypes
                                                         where ptype.Active == true
                                                         orderby ptype.PayTypeCode
                                                         select new
                                                         {
                                                             Key = ptype.PayTypeID,
                                                             Value = ptype.PayTypeCode + " " + ptype.PayTypeDesc,
                                                         }).ToDictionary(o => o.Key, o => o.Value);
            return FrmCustomerPaymentTypeReport;
        }
        public CustomerPaymentTypeReportBData CustomerPaymentTypeReport(int PaymentTypeID, DateTime StartDate, DateTime EndDate)
        {
            CustomerPaymentTypeReportBData Data = new CustomerPaymentTypeReportBData();
            Data.PaymentTypeDetail = (from ptype in _context.tblPayTypes
                                      where ptype.PayTypeID == PaymentTypeID
                                      select new PaymentTypesBData
                                      {
                                          PayTypeCode = ptype.PayTypeCode,
                                          PayTypeDesc = ptype.PayTypeDesc,
                                      }).SingleOrDefault();
            Data.PaymentTypes = (from pay in _context.tblPayments
                                 from paytype in _context.tblPayTypes
                                 from cust in _context.tblCustomers
                                 where pay.PayTypeID == PaymentTypeID
                                 && pay.PaymentDate >= StartDate
                                 && pay.PaymentDate <= EndDate
                                 && pay.PayTypeID == paytype.PayTypeID
                                 && cust.CustomerID == pay.CustomerID
                                 select new CustomerPaymentTypeReportRowBData
                                 {
                                     PaymentDate = pay.PaymentDate,
                                     Description = pay.Description,
                                     LineNo = pay.LineNo,
                                     CustomerID = cust.CustomerID,
                                     CustomerCode = cust.CustomerCode,
                                     OrganizationName = cust.OrganizationName,
                                     Payment = pay.Credit - pay.Debit,
                                     Refunded = pay.RefundF ? (from dep in _context.tblDeposits
                                                               where dep.LineNo == pay.LineNo
                                                               && dep.CustomerID == cust.CustomerID
                                                               select dep.Refunded).FirstOrDefault() : null,
                                 }).Distinct().OrderBy(x => x.PaymentDate).ToList();
            var CustomerIDs = Data.PaymentTypes.Where(row => row.CustomerID > 0).Select(row => row.CustomerID).ToList();
            var Deposits = _context.tblDeposits.Where(dep => CustomerIDs.Contains((int)dep.CustomerID)).ToList();
            var GroupIDs = Deposits.Select(dep => dep.GroupID).ToList();
            var Groups = _context.tblGroups.Where(grp => GroupIDs.Contains(grp.GroupID)).ToList();
            foreach (var row in Data.PaymentTypes)
            {
                row.Rows = (from dep in Deposits
                            from grp in Groups
                            where dep.CustomerID == row.CustomerID
                            && dep.GroupID != 0
                            && dep.GroupID == grp.GroupID
                            && dep.LineNo == row.LineNo
                            select new CustomerPaymentTypeOtherReportRowBData
                            {
                                GroupCode = grp.GroupCode,
                                GroupName = grp.GroupName,
                                Balance = dep.Received - dep.Refunded,
                            }).ToList();
            }
            if (Data.PaymentTypeDetail == null)
                Data.PaymentTypeDetail = new PaymentTypesBData();
            if (!Data.PaymentTypes.Any())
                Data.PaymentTypes = new List<CustomerPaymentTypeReportRowBData>();
            return Data;
        }
        public CustomerRefundReportBData CustomerRefundReport(int CustomerID, DateTime StartDate, DateTime EndDate)
        {
            CustomerRefundReportBData Data = new CustomerRefundReportBData();
            Data.CustomerRefundDetail = (from cust in _context.tblCustomers
                                         where cust.CustomerID == CustomerID
                                         select new CustomerRefundBData
                                         {
                                             CustomerCode = cust.CustomerCode,
                                             OrganizationName = cust.OrganizationName,
                                         }).SingleOrDefault();
            Data.Refunds = (from pay in _context.tblPayments
                            from paytype in _context.tblPayTypes
                            from dep in _context.tblDeposits
                            where pay.CustomerID == CustomerID
                            && dep.LineNo == pay.LineNo
                            && dep.CustomerID == CustomerID
                            && pay.PaymentDate >= StartDate
                            && pay.PaymentDate <= EndDate
                            && pay.PayTypeID == paytype.PayTypeID
                            && pay.RefundF == true
                            && dep.GroupID != 0
                            select new CustomerRefundReportRowBData
                             {
                                 LineNo = dep.LineNo,
                                 PaymentDate = pay.PaymentDate,
                                 Description = pay.Description,
                                 PayTypeCode = paytype.PayTypeCode,
                                 Debit = pay.Debit,
                             }).Distinct().OrderBy(x => x.PaymentDate).ToList();
            var Deposits = _context.tblDeposits.Where(dep => dep.CustomerID == CustomerID && dep.GroupID > 0).ToList();
            var DepositGroupIDs = Deposits.Select(dep => dep.GroupID).ToList();
            var Groups = _context.tblGroups.Where(grp => DepositGroupIDs.Contains(grp.GroupID)).ToList();
            foreach (var row in Data.Refunds)
            {
                row.Rows = (from dep in Deposits
                            from grp in Groups
                            where dep.LineNo == row.LineNo
                            && dep.GroupID == grp.GroupID
                            select new CustomerRefundOtherReportRowBData
                            {
                                GroupCode = grp.GroupCode,
                                GroupName = grp.GroupName,
                                Refunded = dep.Refunded,
                            }).ToList();
            }
            if (Data.CustomerRefundDetail == null)
                Data.CustomerRefundDetail = new CustomerRefundBData();
            if (!Data.Refunds.Any())
                Data.Refunds = new List<CustomerRefundReportRowBData>();
            return Data;
        }
        public FrmGuideListReportBData LoadFrmGuideListReport()
        {
            FrmGuideListReportBData FrmGuideListReport = new FrmGuideListReportBData();

            FrmGuideListReport.Regions = new Dictionary<int, string>();
            FrmGuideListReport.Regions = (from reg in _context.tblRegions
                                          where reg.Active == true
                                          orderby reg.RegionCode
                                          select new
                                          {
                                              Key = reg.RegionID,
                                              Value = reg.RegionCode + " " + reg.RegionDesc,
                                          }).ToDictionary(o => o.Key, o => o.Value);
            return FrmGuideListReport;
        }
        public GuideListReportBData GuideListReport(int RegionID)
        {
            GuideListReportBData Data = new GuideListReportBData();
            Data.RegionCode = (from reg in _context.tblRegions
                               where reg.RegionID == RegionID
                               select reg.RegionCode).SingleOrDefault();
            Data.Guides = (from guide in _context.tblGuides
                           where guide.RegionID == RegionID
                           && guide.Active == true
                           orderby guide.City
                           select new GuideListReportRowBData
                            {
                                City = guide.City,
                                StateID = guide.StateID,
                                Address = guide.Address,
                                Fax = guide.Fax,
                                FirstName = guide.FirstName,
                                LastName = guide.LastName,
                                Note = guide.Note,
                                Phone1 = guide.Phone,
                                Phone2 = guide.Phone2,
                                PostalCode = guide.PostalCode,
                                Sex = guide.Sex,
                            }).ToList();
            if (!Data.Guides.Any())
                Data.Guides = new List<GuideListReportRowBData>();
            return Data;
        }
        public FrmHotelContractsReportBData LoadFrmHotelContractsReport()
        {
            FrmHotelContractsReportBData FrmHotelContractsReport = new FrmHotelContractsReportBData();

            FrmHotelContractsReport.Regions = new Dictionary<int, string>();
            FrmHotelContractsReport.Regions = (from reg in _context.tblRegions
                                               where reg.Active == true
                                               orderby reg.RegionCode
                                               select new
                                               {
                                                   Key = reg.RegionID,
                                                   Value = reg.RegionCode + " " + reg.RegionDesc,
                                               }).ToDictionary(o => o.Key, o => o.Value);
            FrmHotelContractsReport.Seasons = new Dictionary<int, string>();
            FrmHotelContractsReport.Seasons = (from season in _context.tblSeasons
                                               orderby season.SeasonCode
                                               select new
                                               {
                                                   Key = season.SeasonID,
                                                   Value = season.SeasonCode + " " + season.SeasonDescription,
                                               }).ToDictionary(o => o.Key, o => o.Value);
            return FrmHotelContractsReport;
        }
        public HotelContractsReportBData HotelContractsReport(int RegionID, int SeasonID)
        {
            HotelContractsReportBData Data = new HotelContractsReportBData();
            Data.RegionDetail = (from reg in _context.tblRegions
                                 where reg.RegionID == RegionID
                                 select new HotelContractsRegionBData
                                 {
                                     RegionCode = reg.RegionCode,
                                     RegionDesc = reg.RegionDesc,
                                 }).SingleOrDefault();
            Data.Titles = (from stype in _context.tblServiceTypes
                           from service in _context.tblServices
                           where stype.VendorTypeID == 1
                           && service.ServiceTypeID == stype.ServiceTypeID
                           orderby stype.ServiceTypeCode
                           select new HotelContractTitles
                           {
                               ServiceCode = service.ServiceCode,
                               ServiceTypeCode = stype.ServiceTypeCode,
                           }).Distinct().ToList();
            Data.Hotels = (from ven in _context.tblVendors
                           from cnt in _context.tblContracts
                           where ven.RegionID == RegionID
                           && ven.VendorTypeID == 1
                           && ven.Active == true
                           && cnt.SeasonID == SeasonID
                           && cnt.VendorID == ven.VendorID
                           orderby ven.VendorID, cnt.SeasonID
                           select new HotelContractsReportRowBData
                           {
                               VendorName = ven.VendorName,
                               VendorCode = ven.VendorCode,
                               ContactName = ven.ContactName,
                               Address = ven.Address,
                               City = ven.City,
                               StateID = ven.StateID,
                               Fax = ven.FaxNumber,
                               Phone = ven.PhoneNumber,
                               FaxSales = ven.FaxSales,
                               PhoneSales = ven.PhoneSales,
                               PostalCode = ven.PostalCode,
                               Note = cnt.Note,
                               Tax = cnt.Tax,
                               OccTax = cnt.OccTax,
                               Luggage = cnt.Luggage,
                           }).Distinct().ToList();
            if (Data.RegionDetail == null)
                Data.RegionDetail = new HotelContractsRegionBData();
            if (!Data.Titles.Any())
                Data.Titles = new List<HotelContractTitles>();
            if (!Data.Hotels.Any())
                Data.Hotels = new List<HotelContractsReportRowBData>();
            return Data;
        }
        public VConfirmBreakDownReportBData VConfirmBreakDownReport(int VendorID, int GroupID, DateTime ArrivalDate)
        {
            VConfirmBreakDownReportBData Data = new VConfirmBreakDownReportBData();
            Data.BuggageQty = _context.tblVConfirmDetails.Where(vcdet => (vcdet.VendorID == VendorID) && (vcdet.GroupID == GroupID) && (vcdet.ArrivalDate == ArrivalDate) && (vcdet.ServiceID == 103)).Select(vcdet=>vcdet.Qty * vcdet.Price).Sum();
            Data.BreakDownAllDetail = (from grp in _context.tblGroups
                                       from vcnf in _context.tblVendorConfirms
                                       where grp.GroupID == GroupID
                                       && vcnf.ArrivalDate == ArrivalDate
                                       && vcnf.GroupID == GroupID
                                       && vcnf.VendorID == VendorID
                                       select new VConfirmBreakDownReportAll
                                       {
                                           Less = vcnf.Less,
                                           LessARoom = vcnf.LessARoom,
                                           LessNRoom = vcnf.LessNRoom,
                                           Comp = vcnf.Comp,
                                           Baggage = vcnf.Baggage,
                                           LessDeposit = (vcnf.FirstPayment != null ? vcnf.FirstPayment : 0) + (vcnf.SecondPayment != null ? vcnf.SecondPayment : 0) + (vcnf.ThirdPayment != null ? vcnf.ThirdPayment : 0),
                                           SumOfSentE = (from dep in _context.tblDeposits
                                                         where dep.DepositTypeID == 2
                                                         && dep.VendorID == VendorID
                                                         && dep.GroupID == GroupID
                                                         select dep.Sent).Sum(),
                                       }).SingleOrDefault();
            Data.CompanyDetail = (from cmp in _context.tblCompanies
                                  where cmp.CompanyID == 1
                                  select new CompanyBData
                                  {
                                      CompanyName = cmp.CompanyName,
                                      Address = cmp.Address,
                                      City = cmp.City,
                                      State = cmp.StateID,
                                      PostalCode = cmp.PostalCode,
                                      PhoneNumber = cmp.PhoneNumber,
                                      FaxNumber = cmp.FaxNumber,
                                  }).SingleOrDefault();
            Data.VendorDetail = (from vnd in _context.tblVendors
                                 where vnd.VendorID == VendorID
                                 select new VendorBData
                                 {
                                     VendorName = vnd.VendorName,
                                     VendorPhoneNumber = vnd.PhoneNumber,
                                     VendorFaxNumber = vnd.FaxNumber,
                                     ContactName = vnd.ContactName,
                                     ContactTitle = vnd.ContactTitle,
                                 }).SingleOrDefault();
            Data.GroupDetail = (from grp in _context.tblGroups
                                from vcnf in _context.tblVendorConfirms
                                where grp.GroupID == GroupID
                                && vcnf.GroupID == GroupID
                                && vcnf.VendorID == VendorID
                                && vcnf.ArrivalDate == ArrivalDate
                                select new GroupBData
                                 {
                                     GroupCode = grp.GroupCode,
                                     GroupName = grp.GroupName,
                                     Nationality = (from cnt in _context.tblCountries where cnt.CountryID == grp.CountryID select cnt.Nationality).FirstOrDefault(),
                                     Pax = vcnf.Pax,
                                     PaxPlus = vcnf.PaxPlus,
                                     GuideCount = (from vcnfdet in _context.tblVConfirmDetails 
                                                   where vcnfdet.GroupID == GroupID 
                                                   && vcnfdet.VendorID == VendorID
                                                   && vcnfdet.ArrivalDate == ArrivalDate
                                                   && vcnfdet.ServiceID == 87
                                                   select vcnfdet.Qty).FirstOrDefault(),
                                     Luggage = vcnf.Luggage,
                                     OccTax = vcnf.OccTax,
                                     Tax = vcnf.Tax,
                                     VendorRef = vcnf.VendorRef,
                                     ArrivalDate = vcnf.ArrivalDate,
                                     ArrivalTime = vcnf.ArrivalTime,
                                     DepartureDate = vcnf.DepartureDate,
                                     DepartureTime = vcnf.DepartureTime,
                                 }).SingleOrDefault();
            Data.BreakDown = (from vcnf in _context.tblVendorConfirms
                              from vcnfdet in _context.tblVConfirmDetails
                              from service in _context.tblServices
                              where vcnf.VendorID == VendorID
                              && vcnf.GroupID == GroupID
                              && vcnfdet.VendorID == VendorID
                              && vcnfdet.GroupID == GroupID
                              && vcnfdet.ArrivalDate == ArrivalDate
                              && service.ServiceID == vcnfdet.ServiceID
                              && vcnfdet.ManualFlg != true
                              select new VConfirmBreakDownReportRowBData
                              {
                                  ArrivalDate = vcnfdet.ArrivalDate,
                                  StartDate = vcnfdet.StartDate,
                                  EndDate = vcnfdet.EndDate,
                                  Price = vcnfdet.Price,
                                  ProductID = vcnfdet.ProductID,
                                  Qty = vcnfdet.Qty,
                                  ServiceCode = service.ServiceCode,
                                  ServiceTypeCode = (from stype in _context.tblServiceTypes where stype.ServiceTypeID == service.ServiceTypeID select stype.ServiceTypeCode).FirstOrDefault(),
                                  ServiceTypeID = service.ServiceTypeID,
                                  ServiceID = service.ServiceID,
                              }).Distinct().OrderBy(row => new { row.ServiceTypeID, row.ServiceID }).ToList();
            Data.BreakDownExtra = (from vcnf in _context.tblVendorConfirms
                                   from vcnfdet in _context.tblVConfirmDetails
                                   from service in _context.tblServices
                                   where vcnf.VendorID == VendorID
                                   && vcnf.GroupID == GroupID
                                   && vcnfdet.VendorID == VendorID
                                   && vcnfdet.GroupID == GroupID
                                   && vcnfdet.ArrivalDate == ArrivalDate
                                   && service.ServiceID == vcnfdet.ServiceID
                                   && vcnfdet.ManualFlg == true
                                   select new VConfirmBreakDownReportRowBData
                                   {
                                       ArrivalDate = vcnfdet.ArrivalDate,
                                       StartDate = vcnfdet.StartDate,
                                       EndDate = vcnfdet.EndDate,
                                       Price = vcnfdet.Price,
                                       Qty = vcnfdet.Qty,
                                       ServiceCode = service.ServiceCode,
                                       ServiceTypeCode = (from stype in _context.tblServiceTypes where stype.ServiceTypeID == service.ServiceTypeID select stype.ServiceTypeCode).FirstOrDefault(),
                                       ServiceTypeID = service.ServiceTypeID,
                                       ServiceID = service.ServiceID,
                                   }).Distinct().OrderBy(row => new { row.ServiceTypeID, row.ServiceID }).ToList();
            if (Data.BreakDownAllDetail == null)
                Data.BreakDownAllDetail = new VConfirmBreakDownReportAll();
            if (Data.CompanyDetail == null)
                Data.CompanyDetail = new CompanyBData();
            if (Data.VendorDetail == null)
                Data.VendorDetail = new VendorBData();
            if (Data.GroupDetail == null)
                Data.GroupDetail = new GroupBData();
            if (!Data.BreakDown.Any())
                Data.BreakDown = new List<VConfirmBreakDownReportRowBData>();
            if (!Data.BreakDownExtra.Any())
                Data.BreakDownExtra = new List<VConfirmBreakDownReportRowBData>();
            return Data;
        }
        public OperationAmexVendorReportBData AuthorizationFormReport(int AuthFormID)
        {
            var GroupID = Convert.ToInt32(_context.tblAuthForms.Where(autf => autf.AuthFormID == AuthFormID).Select(autf => autf.GroupID).SingleOrDefault());
            var VendorID = Convert.ToInt32(_context.tblAuthForms.Where(autf => autf.AuthFormID == AuthFormID).Select(autf => autf.VendorID).SingleOrDefault());
            var CardID = Convert.ToInt32(_context.tblAuthForms.Where(autf => autf.AuthFormID == AuthFormID).Select(autf => autf.CardID).SingleOrDefault());
            var LineNo = Convert.ToInt32(_context.tblAuthForms.Where(autf => autf.AuthFormID == AuthFormID).Select(autf => autf.LineNo).SingleOrDefault());
            OperationAmexVendorReportBData AmexBData = new OperationAmexVendorReportBData();
            var Company = _context.tblCompanies.Where(cmp => cmp.CompanyID == 1).SingleOrDefault();
            if (Company != null)
            {
                AmexBData.CompanyAddress = Company.Address + ", " + Company.City + ", " + Company.StateID + ", " + Company.PostalCode;
                AmexBData.CompanyPhone = Company.PhoneNumber;
                AmexBData.CompanyFax = Company.FaxNumber;
            }
            var Grp = _context.tblGroups.Where(grp => grp.GroupID == GroupID).SingleOrDefault();
            var Vnd = _context.tblVendors.Where(vnd => vnd.VendorID == VendorID).SingleOrDefault();
            var Card = _context.tblCards.Where(crd => crd.CardID == CardID).SingleOrDefault();
            if (Grp != null)
            {
                AmexBData.GroupCode = Grp.GroupCode;
                AmexBData.GroupID = Grp.GroupID;
                AmexBData.GroupName = Grp.GroupName;
            }
            if (Vnd != null)
            {
                AmexBData.VendorID = Vnd.VendorID;
                AmexBData.VendorName = Vnd.VendorName;
            }
            if (Card != null)
            {
                var CardType = _context.tblCardTypes.Where(crdt => crdt.CardTypeID == Card.CardTypeID).SingleOrDefault();
                if (CardType != null)
                {
                    AmexBData.CardType = CardType.CardType;
                }
                AmexBData.CardNumber = Card.CardNumber;
                CardID = Card.CardID;
                AmexBData.PlaceHolderName = Card.PlaceHolderName;
                AmexBData.CardValidToDate = Card.CardValidToDate;
            }
            var AmexVendor = _context.tblAuthForms.AsQueryable();
            if (AmexVendor.Any(amex => amex.VendorID == VendorID && amex.GroupID == GroupID && amex.CardID == CardID && amex.LineNo == LineNo))
            {
                AmexVendor = AmexVendor.Where(amex => amex.VendorID == VendorID && amex.GroupID == GroupID && amex.CardID == CardID && amex.LineNo == LineNo);
            }
            else if (AmexVendor.Any(amex => amex.VendorID == VendorID && amex.GroupID == GroupID))
            {
                AmexVendor = AmexVendor.Where(amex => amex.VendorID == VendorID && amex.GroupID == GroupID);
            }
            else
            {
                AmexBData.AmexVendorID = -1;
                return AmexBData;
            }
            var Data = (from AmxVnd in AmexVendor
                        select new OperationAmexVendorReportBData
                        {
                            AmexVendorID = AmxVnd.AuthFormID,
                            PreparedBy = AmxVnd.Preparedby,
                            Date = AmxVnd.PaymentDate,
                            Attention = AmxVnd.Attention,
                            LineNo = AmxVnd.LineNo,
                            CardID = AmxVnd.CardID,
                            AuthorizedAmount = AmxVnd.AuthAmt,
                            PaymentFor = AmxVnd.PaymentFor,
                            Curr = AmxVnd.Curr,
                        }).FirstOrDefault();
            if (Data != null)
            {
                AmexBData.AmexVendorID = Data.AmexVendorID;
                AmexBData.PreparedBy = Data.PreparedBy;
                AmexBData.Date = Data.Date;
                AmexBData.Attention = Data.Attention;
                AmexBData.AuthorizedAmount = Data.AuthorizedAmount;
                AmexBData.PaymentFor = Data.PaymentFor;
                AmexBData.LineNo = Data.LineNo;
                AmexBData.CardID = Data.CardID;
                AmexBData.Curr = Data.Curr;
            }

            return AmexBData;
        }
        public Dictionary<int, string> FindKeywordInEntity(string PropName, string keyword, string TabName)
        {
            Dictionary<int, string> RetVal = new Dictionary<int, string>();
            try
            {
                if (PropName == "ReportSelectedVendorName")
                {
                    switch (TabName)
                    {
                        case "Hotel":
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true && vnd.VendorTypeID == 1).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                        case "Attractions":
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true && (vnd.VendorTypeID == 4 || vnd.VendorTypeID == 5)).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                        case "Restaurants":
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true && vnd.VendorTypeID == 2).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                        case "Bus":
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true && vnd.VendorTypeID == 3).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                        case "Misc":
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true && (vnd.VendorTypeID == 6 || vnd.VendorTypeID == 7 || vnd.VendorTypeID == 9)).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                        case "Guide":
                            {
                                RetVal = _context.tblGuides.Where(guide => (guide.FirstName + " " + guide.LastName).Contains(keyword) && guide.Active == true).Select(guide => new { guide.GuideID, guide.FirstName, guide.LastName }).Take(10).ToDictionary(guide => guide.GuideID, guide => guide.FirstName + " " + guide.LastName);
                                return RetVal;
                            }
                        case "Group CheckList":
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                        default:
                            {
                                RetVal = _context.tblVendors.Where(vnd => vnd.VendorName.Contains(keyword) && vnd.Active == true).Select(vnd => new { vnd.VendorID, vnd.VendorName }).Take(10).ToDictionary(vnd => vnd.VendorID, vnd => vnd.VendorName);
                                return RetVal;
                            }
                    };
                }
                if (PropName == "ReportSelectedGuideName")
                {
                    RetVal = _context.tblGuides.Where(guide => (guide.FirstName + " " + guide.LastName).Contains(keyword) && guide.Active == true).Select(guide => new { guide.GuideID, guide.FirstName, guide.LastName }).Take(10).ToDictionary(guide => guide.GuideID, guide => guide.FirstName + " " + guide.LastName);
                    return RetVal;
                }
                if (PropName == "ReportSelectedCustomerOrgName")
                {
                    RetVal = _context.tblCustomers.Where(cust => cust.OrganizationName.Contains(keyword) && cust.Active == true).Select(cust => new { cust.CustomerID, cust.OrganizationName }).Take(10).ToDictionary(cust => cust.CustomerID, cust => cust.OrganizationName);
                    return RetVal;
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex.ToMessageAndCompleteStacktrace());
            }
            return RetVal;
        }
        public void Dispose()
        {
            _context.Dispose();
        }
    }
}
